create or replace package body udm_p_thirdExpandDATA is

    procedure udm_p_thirdhedge(v_policy_no in varchar2,v_endor_no in varchar2,v_sysday in varchar2,v_sys_source in varchar2)is
        v_mainid varchar2(32);
        v_endor_type varchar2(32);
        v_updatedfield varchar2(500);
        v_saccounting_period varchar2(10);/*新账期*/
        v_us_accounting_period varchar2(10);/*新账期*/
        v_count  number(5):=0;
        v_premium_amount number(18,2):=0;
        v_extax_premium_amount number(18,2):=0;
        v_premium_amount_cny number(18,2):=0;
        v_extax_premium_amount_cny number(18,2):=0;
        v_topremium_amount number(18,2):=0;
        v_toextax_premium_amount number(18,2):=0;
        v_totax_amount number(18,2):=0;
        v_topremium_amount_cny number(18,2):=0;
        v_toextax_premium_amount_cny number(18,2):=0;
        v_totax_amount_cny number(18,2):=0;
        --v_hedgeid varchar2(4000);/*待对冲的id*/
        v_newMainID varchar2(32);
        v_newMainID2 varchar2(32);
        v_fee_amount number(18,2):=0;
        v_fee_amount_cny number(18,2):=0;

        --对冲冲正时需要使用缴费计划表最新信息
        /* v_debitnote_no  varchar2(30);
         v_crct_no  varchar2(60);
         v_is_installment  varchar2(1);
         v_installment_sn varchar2(10);
         v_installment_times number(5);
         v_pay_start_date date;
         v_payer_code varchar2(30);
         v_payer_name varchar2(100);*/

        --对冲冲正时需要使用责任表最新信息
        v_treaty_type varchar2(2);
        v_ht_treaty_share number(8,4);
        v_treaty_group varchar2(20);
        v_k_start_date date;
        v_k_end_date date;
        v_k_valid_date date;
        v_pay_start_datr date;
        v_subject_name  varchar2(60);

        v_commfee_endorno  varchar2(25);/*用于冲减时获取最新中介信息对应的批单号*/
        v_salefee_endorno  varchar2(25);
        v_salary_endorno   varchar2(25);
        v_confirmation_code varchar2(30);

        v_producer_type varchar2(2);
        v_producer_code varchar2(20);

        v_line_of_business varchar2(20); --三级分类代码 12CHSXHX为火险
        v_earned_startdate_enddate_hedge_way varchar(1);
        v_new_earned_startdate date;
        v_new_earned_enddate date;
        v_riskcode varchar2(4);
        v_lineofbusiness_lv4 varchar2(20);

        /**
          对冲分2种情形，1、对旧金额全部冲负，然后按照最新基本信息、旧金额冲正--完全在此模块实现
                         2、对旧金额全部冲负，冲正金额按照最新保费计算--冲负在此模块实现，冲正在各个模块实现
          HEDGE_FLAG：F-冲负，Z-冲正，N-非对冲数据
          特殊处理：冲负数据的total都是0;车险停复驶场景无需对冲
          存在对冲字段以及保费变化符合第1种对冲情况，只冲负，冲正重新算
        */

begin
select tt.endor_type,tt.updated_fields,tt.statutory_accounting_period,tt.us_accounting_period,tt.premium_amount,tt.line_of_business,tt.risk_code
into v_endor_type,v_updatedfield,v_saccounting_period,v_us_accounting_period,v_premium_amount, v_line_of_business, v_riskcode
from udm_policy_main tt where tt.policy_no=v_policy_no and tt.endor_no=v_endor_no;

select prod_lv4_code into v_lineofbusiness_lv4 from htudm.udm_lineofbusiness_code t where is_deleted = 0 and riskcode = v_riskcode;

--容错处理，避免前端在非可修改的情况下送入不该送的对冲字段
--只有在修改数据的情况下，归属机构、业务来源、生效日期、交易日期、财务挂账日期、业务签单账期才会改变，各核心将正常批改时也送了变化，剔除此种情况
if v_endor_type!='1090' and v_endor_type!='1091' and v_endor_type!='1092' then
            v_updatedfield := replace(v_updatedfield,'P001','');
            v_updatedfield := replace(v_updatedfield,'P002','');
            v_updatedfield := replace(v_updatedfield,'P009','');
            v_updatedfield := replace(v_updatedfield,'P010','');
            v_updatedfield := replace(v_updatedfield,'P011','');
            v_updatedfield := replace(v_updatedfield,'P045','');
            v_updatedfield := replace(v_updatedfield,'P046','');
            v_updatedfield := replace(v_updatedfield,'P001','');
            v_updatedfield := replace(v_updatedfield,'P002','');
            v_updatedfield := replace(v_updatedfield,',,',',');
end if;
        --只有在修改数据的情况下，才允许批改联共保信息
        if v_endor_type!='1090' then
            v_updatedfield := replace(v_updatedfield,'P022','');
            v_updatedfield := replace(v_updatedfield,'P023','');
            v_updatedfield := replace(v_updatedfield,'P024','');
            v_updatedfield := replace(v_updatedfield,'P025','');
            v_updatedfield := replace(v_updatedfield,'P026','');
            v_updatedfield := replace(v_updatedfield,'P027','');
            v_updatedfield := replace(v_updatedfield,'P028','');
            v_updatedfield := replace(v_updatedfield,'P029','');
            v_updatedfield := replace(v_updatedfield,',,',',');
end if;
        v_updatedfield := replace(v_updatedfield,',,',',');

        --车险手续费表投保/批改确认码在冲负时需要使用最新的
select count(1) into v_count from udm_policy_comm_fee a where  a.endor_no = v_endor_no;
if v_count>0 then
select a.confirmation_code into v_confirmation_code from udm_policy_comm_fee a where  a.endor_no = v_endor_no and rownum=1;
end if;

        --赚取起止期
        if v_line_of_business = '12CHSXHX' then --火险
            if v_premium_amount != 0 and (instr(v_updatedfield,'P005')>0 or instr(v_updatedfield,'P006')>0) then --火险--有保费变动且更改保险期间
                v_earned_startdate_enddate_hedge_way := '1';
else --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）
                v_earned_startdate_enddate_hedge_way := '2';
end if;
else --非火险
            v_earned_startdate_enddate_hedge_way := '3';
end if;

        --对冲1：只冲负，此处不冲正；冲正数据在下面保费、费用模块按最新保费重新算
        if instr(v_updatedfield,'P031')>0 --代理人代码
               or instr(v_updatedfield,'P032')>0 --经纪人代码
               or v_endor_type ='1008' --变更缴费计划
               or v_endor_type='1090' --修数批改-保费金额错误修正
               or instr(v_updatedfield,'P054')>0 -- 整机厂保年限
               or v_endor_type='1009' -- 批改业务员(录入错误)
               or instr(v_updatedfield,'P055')>0 -- 变更投保人
               or instr(v_updatedfield,'P033')>0 -- 申报号
               or instr(v_updatedfield,'P034')>0 -- 工程险保证期天数
               or instr(v_updatedfield,'P035')>0 -- UPC标记
               or instr(v_updatedfield,'P036')>0 -- MN业务
               or v_endor_type='1001' -- 1001 变更保险期限（问题53）
               or instr(v_updatedfield,'P005')>0 -- 保单保险起期
               or instr(v_updatedfield,'P006')>0 -- 保单保险止期
               or v_endor_type='1010' --费用批改
               or v_endor_type='1040' --费用批改
               or (length(v_updatedfield)>3 and v_endor_type!='1010')
               --or (v_premium_amount!=0 and length(v_updatedfield)>3 and instr(v_updatedfield,'P')>0)
	    then
            --1、仅批改代理人或经纪人
            --2、批改缴费计划或者批改联共保信息，保费、费用冲负，正数正常算
            --3、批改类型1090-完全冲减掉旧的保费、费用金额，按照新的拆分
            FOR p IN (SELECT tpremium1.id,tpremium1.endor_no,tpremium1.kind_sn FROM UDM_P_THIRDPREMIUM tpremium1 WHERE TPREMIUM1.policy_no=v_policy_no
                                                                                                                   AND NOT EXISTS (SELECT 1 FROM UDM_P_THIRDPREMIUM tpremium2 WHERE tpremium2.hedge_thirdid=tpremium1.id
                                                                                                                                                                                AND tpremium2.hedge_thirdid IS NOT null) AND tpremium1.hedge_thirdid IS NULL )LOOP

select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_TPremiumid.Nextval, 12, '0') into v_newMainID from dual;
select earned_startdate,earned_enddate into v_new_earned_startdate, v_new_earned_enddate from UDM_POLICY_COVERAGE where policy_no=v_policy_no and endor_no=v_endor_no and rownum=1;

insert into UDM_P_THIRDPREMIUMTEMP
(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,
 endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
 risk_code ,start_date,end_date,issue_date,uw_date ,
 transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
 sale_system ,init_policy_no,inception_date,renewed_policy_no ,business_source ,business_type ,appli_code,
 nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,insuredname,is_multi_insureds ,operator_code ,
 guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,
 is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
 debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,crct_no,installment_sn,
 installment_times ,payer_code,payer_name,coins_code,coins_name,
 coins_share ,coins_fee_rate,coins_type,coins_flag,
 joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,
 joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,
 treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
 k_end_date,k_valid_date,k_valid_date_old,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,
 suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
 premium_amount,premium_currency,premium_exrate,premium_amount_cny,vat_flag,taxfree_flag,tax_rate,
 extax_premium_amount,tax_amount,extax_premium_amount_cny,tax_amount_cny,
 totalpremium_amount,totalextax_premium_amount,totaltax_amount,
 totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,REFLAG,
 PAY_START_DATE,SUBJECT_NAME,policy_from,business_unit,warranty_period,pre_crctno
    ,producer_type, producer_code, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,accperiod_new,iscbc,isgiftinsurance)

select
    v_newMainID,tt3.id,tt3.sys_source,tt3.proposal_no,tt1.policy_no ,tt1.declaration_no,
    tt3.endorApply_no,tt3.endor_no,tt3.endor_sn,tt3.endor_type,tt1.cancel_reason ,tt1.issue_company ,
    tt1.risk_code ,tt1.start_date,tt1.end_date,tt1.issue_date,tt3.uw_date ,
    tt3.transaction_date,case when instr(v_updatedfield,'P005')>0 then tt1.valid_date else tt3.valid_date end,tt1.renew_flag,
    case when instr(v_updatedfield,'P005')>0 then
             case when greatest(tt1.start_date,tt3.uw_date) >= TO_DATE('2024-12-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS') then
                      --2024年12月25日后 用新逻辑
                      greatest(tt1.start_date,tt1.valid_date,tt3.uw_date)
                  else
                      --2024年12月25日前 用老逻辑
                      greatest(tt1.start_date,tt3.uw_date)
                 end
         else
             tt3.statutory_accounting_period
        END,
    tt3.us_accounting_period,
    tt1.sale_system ,tt1.init_policy_no,tt1.inception_date,tt1.renewed_policy_no, tt1.business_source ,tt1.business_type ,tt1.appli_code,
    tt1.nationalitycode,tt1.enterprisenature,tt1.htrelationship,tt1.circdomesticind,tt1.appliename,tt1.insuredname,tt1.is_multi_insureds ,tt1.operator_code ,
    tt1.guarantee_days,tt1.upc_flag,tt1.domestic_flag ,tt1.internet_flag ,tt1.policy_type ,
    tt1.is_prepaid,tt1.is_oversea,tt1.is_renewable,tt1.policy_renewalbe_no ,tt1.business_year ,
    tt1.debitnote_no,tt1.dm_flag ,tt1.hisdata_flag,tt1.modifydata_flag ,tt1.crct_no||'A',tt1.installment_sn,
    tt1.installment_times ,tt1.payer_code,tt1.payer_name,tt1.coins_code,tt1.coins_name,
    tt1.coins_share ,tt1.coins_fee_rate,tt1.coins_type,tt1.coins_flag,
    tt1.joins_code,tt1.joins_name,tt1.joins_share ,tt1.joins_policy_no ,tt1.joins_endor_no,
    tt1.joins_type,tt1.joins_flag,tt1.kind_sn ,tt1.kind_code ,tt1.kind_name ,tt1.kind_type ,
    tt1.treaty_type ,tt1.ht_treaty_share ,tt1.treaty_group,tt1.k_start_date,
    tt1.k_end_date,tt3.valid_date,tt1.k_valid_date,(-1)*suminsured_amount ,(-1)*tt1.deductible_amount ,tt1.suminsured_currency ,tt1.suminsured_exrate ,
    (-1)*tt1.suminsured_amount_cny ,(-1)*tt1.deductible_amount_cny ,tt1.deductible_rate ,
    (-1)*tt1.premium_amount,tt1.premium_currency,tt1.premium_exrate,(-1)*tt1.premium_amount_cny,tt1.vat_flag,tt1.taxfree_flag,tt1.tax_rate,
    (-1)*tt1.extax_premium_amount,(-1)*tt1.tax_amount,(-1)*tt1.extax_premium_amount_cny,(-1)*tt1.tax_amount_cny,
    0,0,0,
    0,0,0,tt1.second_main_id,tt1.id,'F',
    tt1.pay_start_date,tt1.subject_name,tt1.policy_from,tt1.business_unit,tt1.warranty_period,tt1.crct_no
        ,tt1.producer_type, tt1.producer_code,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_startdate --火险--有保费变动且更改保险期间	新单赚取起期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_startdate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）	旧单赚取起期
        else tt1.earned_startdate --其余场景（非火险以外的其余对冲场景）	旧单赚取起期
        end,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_enddate --火险--有保费变动且更改保险期间 新单赚取止期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_enddate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑） 旧单赚取止期
        else tt1.earned_enddate --其余场景（非火险以外的其余对冲场景） 旧单赚取止期
        end,
    v_line_of_business,v_lineofbusiness_lv4,
    case when instr(v_updatedfield,'P005')>0 then greatest(tt1.start_date,tt3.uw_date,tt1.valid_date) else greatest(tt3.statutory_accounting_period,tt3.valid_date) END,
    tt1.iscbc,tt1.isgiftinsurance
from UDM_P_THIRDPREMIUM tt1,udm_policy_main tt3
where tt1.policy_no=tt3.policy_no and tt3.endor_no= v_endor_no
  AND tt1.id = p.id;

insert into UDM_P_THIRDCOMM_FEEtemp(id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
                                    operator_code,agreement_code,producer_type,producer_code,producer_name,is_main_producer,producer_tax_rate,fee_rate,
                                    fee_amount,extax_fee_amount, tax_amount,currency,exrate,fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
                                    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG,crct_no)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0'),v_newMainID,tt1.second_id,tt3.policy_no,tt3.endor_no,tt3.endor_sn,
       tt1.operator_code,tt1.agreement_code,tt1.producer_type,tt1.producer_code,tt1.producer_name,tt1.is_main_producer,tt1.producer_tax_rate,tt1.fee_rate,
       (-1)*tt1.fee_amount,(-1)*tt1.extax_fee_amount, (-1)*tt1.tax_amount,tt1.currency,tt1.exrate,(-1)*tt1.fee_amount_cny,(-1)*tt1.extax_fee_amount_cny,(-1)*tt1.tax_amount_cny,
       tt1.ccaltype,tt1.certificateno,v_confirmation_code,tt1.inquery_date,tt1.main_id,tt1.id,'F',tt1.crct_no
from UDM_P_THIRDCOMM_FEE tt1,udm_policy_main tt3
where tt1.policy_no=tt3.policy_no
  and tt3.endor_no=v_endor_no
  and tt1.main_id = p.id;

insert into UDM_P_THIRDSALES_FEEtemp(
    id,main_id, second_id, policy_no,endor_no,crct_no,
    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
    hedge_thirdid,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalefeeid.Nextval, 12, '0'),v_newMainID, tt1.second_id, tt3.policy_no,tt3.endor_no,tt1.crct_no,
       tt1.fee_type,tt1.operator_code,tt1.fee_rate,(-1)*tt1.fee_amount,(-1)*tt1.extax_fee_amount,
       (-1)*tt1.tax_amount,tt1.currency,tt1.tax_rate,(-1)*tt1.fee_amount_cny,(-1)*tt1.extax_fee_amount_cny,
       (-1)*tt1.tax_amount_cny,tt1.ccaltype,tt1.is_fixed_rate,tt1.main_id,
       tt1.id,'F'
from  UDM_P_THIRDSALES_FEE tt1,udm_policy_main tt3
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt1.main_id = p.id;

insert into UDM_P_THIRD_SALARYTEMP(
    id,main_id,second_id,policy_no ,endor_no,
    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
    ccaltype,hedge_sencondid,hedge_thirdid,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalaryid.Nextval, 12, '0'),v_newMainID,tt1.second_id,tt3.policy_no ,tt3.endor_no,
       tt1.crct_no,tt1.operator_code,tt1.fee_rate,(-1)*tt1.fee_amount,(-1)*tt1.fee_amount_cny,
       tt1.ccaltype,tt1.main_id,tt1.id,'F'
from UDM_P_THIRD_SALARY tt1,udm_policy_main tt3
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt1.main_id = p.id;
end loop;
	 --处理归档表的数据
FOR p IN (SELECT tpremium1.id,tpremium1.endor_no,tpremium1.kind_sn FROM UDM_P_THIRDPREMIUM_Archive tpremium1 WHERE TPREMIUM1.policy_no=v_policy_no
              AND NOT EXISTS (SELECT 1 FROM UDM_P_THIRDPREMIUM_Archive tpremium2 WHERE tpremium2.hedge_thirdid=tpremium1.id
              AND tpremium2.hedge_thirdid IS NOT null) AND tpremium1.hedge_thirdid IS NULL )LOOP

select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_TPremiumid.Nextval, 12, '0') into v_newMainID from dual;

select earned_startdate,earned_enddate into v_new_earned_startdate, v_new_earned_enddate from UDM_POLICY_COVERAGE where policy_no=v_policy_no and endor_no=v_endor_no and rownum=1;

insert into UDM_P_THIRDPREMIUMTEMP
(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,
 endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
 risk_code ,start_date,end_date,issue_date,uw_date ,
 transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
 sale_system ,init_policy_no,inception_date,renewed_policy_no ,business_source ,business_type ,appli_code,
 nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,insuredname,is_multi_insureds ,operator_code ,
 guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,
 is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
 debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,crct_no,installment_sn,
 installment_times ,payer_code,payer_name,coins_code,coins_name,
 coins_share ,coins_fee_rate,coins_type,coins_flag,
 joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,
 joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,
 treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
 k_end_date,k_valid_date,k_valid_date_old,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,
 suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
 premium_amount,premium_currency,premium_exrate,premium_amount_cny,vat_flag,taxfree_flag,tax_rate,
 extax_premium_amount,tax_amount,extax_premium_amount_cny,tax_amount_cny,
 totalpremium_amount,totalextax_premium_amount,totaltax_amount,
 totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,REFLAG,
 PAY_START_DATE,SUBJECT_NAME,policy_from,business_unit,warranty_period,pre_crctno
    ,producer_type, producer_code, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,accperiod_new,iscbc,isgiftinsurance)

select
    v_newMainID,tt3.id,tt3.sys_source,tt1.proposal_no,tt1.policy_no ,tt1.declaration_no,
    tt3.endorApply_no,tt3.endor_no,tt3.endor_sn,tt3.endor_type,tt1.cancel_reason ,tt1.issue_company ,
    tt1.risk_code ,tt1.start_date,tt1.end_date,tt1.issue_date,tt3.uw_date ,
    tt3.transaction_date,case when instr(v_updatedfield,'P005')>0 then tt1.valid_date else tt3.valid_date end,tt1.renew_flag,case when instr(v_updatedfield,'P005')>0 then tt1.statutory_accounting_period else greatest(tt3.statutory_accounting_period,tt3.uw_date) END, tt3.us_accounting_period,
    tt1.sale_system ,tt1.init_policy_no,tt1.inception_date,tt1.renewed_policy_no, tt1.business_source ,tt1.business_type ,tt1.appli_code,
    tt1.nationalitycode,tt1.enterprisenature,tt1.htrelationship,tt1.circdomesticind,tt1.appliename,tt1.insuredname,tt1.is_multi_insureds ,tt1.operator_code ,
    tt1.guarantee_days,tt1.upc_flag,tt1.domestic_flag ,tt1.internet_flag ,tt1.policy_type ,
    tt1.is_prepaid,tt1.is_oversea,tt1.is_renewable,tt1.policy_renewalbe_no ,tt1.business_year ,
    tt1.debitnote_no,tt1.dm_flag ,tt1.hisdata_flag,tt1.modifydata_flag ,tt1.crct_no||'A',tt1.installment_sn,
    tt1.installment_times ,tt1.payer_code,tt1.payer_name,tt1.coins_code,tt1.coins_name,
    tt1.coins_share ,tt1.coins_fee_rate,tt1.coins_type,tt1.coins_flag,
    tt1.joins_code,tt1.joins_name,tt1.joins_share ,tt1.joins_policy_no ,tt1.joins_endor_no,
    tt1.joins_type,tt1.joins_flag,tt1.kind_sn ,tt1.kind_code ,tt1.kind_name ,tt1.kind_type ,
    tt1.treaty_type ,tt1.ht_treaty_share ,tt1.treaty_group,tt1.k_start_date,
    tt1.k_end_date,tt3.valid_date,tt1.k_valid_date,(-1)*suminsured_amount ,(-1)*tt1.deductible_amount ,tt1.suminsured_currency ,tt1.suminsured_exrate ,
    (-1)*tt1.suminsured_amount_cny ,(-1)*tt1.deductible_amount_cny ,tt1.deductible_rate ,
    (-1)*tt1.premium_amount,tt1.premium_currency,tt1.premium_exrate,(-1)*tt1.premium_amount_cny,tt1.vat_flag,tt1.taxfree_flag,tt1.tax_rate,
    (-1)*tt1.extax_premium_amount,(-1)*tt1.tax_amount,(-1)*tt1.extax_premium_amount_cny,(-1)*tt1.tax_amount_cny,
    0,0,0,
    0,0,0,tt1.second_main_id,tt1.id,'F',
    tt1.pay_start_date,tt1.subject_name,tt1.policy_from,tt1.business_unit,tt1.warranty_period,tt1.crct_no
        ,tt1.producer_type, tt1.producer_code,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_startdate --火险--有保费变动且更改保险期间	新单赚取起期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_startdate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）	旧单赚取起期
        else tt1.earned_startdate --其余场景（非火险以外的其余对冲场景）	旧单赚取起期
        end,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_enddate --火险--有保费变动且更改保险期间 新单赚取止期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_enddate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑） 旧单赚取止期
        else tt1.earned_enddate --其余场景（非火险以外的其余对冲场景） 旧单赚取止期
        end,
    v_line_of_business,v_lineofbusiness_lv4,
    case when instr(v_updatedfield,'P005')>0 then greatest(tt1.start_date,tt3.uw_date,tt1.valid_date) else greatest(tt3.statutory_accounting_period,tt3.valid_date) END,
    tt1.iscbc,tt1.isgiftinsurance
from UDM_P_THIRDPREMIUM_Archive tt1,udm_policy_main tt3
where tt1.policy_no=tt3.policy_no and tt3.endor_no= v_endor_no
  AND tt1.id = p.id;

insert into UDM_P_THIRDCOMM_FEEtemp(id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
                                    operator_code,agreement_code,producer_type,producer_code,producer_name,is_main_producer,producer_tax_rate,fee_rate,
                                    fee_amount,extax_fee_amount, tax_amount,currency,exrate,fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
                                    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG,crct_no)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0'),v_newMainID,tt1.second_id,tt3.policy_no,tt3.endor_no,tt3.endor_sn,
       tt1.operator_code,tt1.agreement_code,tt1.producer_type,tt1.producer_code,tt1.producer_name,tt1.is_main_producer,tt1.producer_tax_rate,tt1.fee_rate,
       (-1)*tt1.fee_amount,(-1)*tt1.extax_fee_amount, (-1)*tt1.tax_amount,tt1.currency,tt1.exrate,(-1)*tt1.fee_amount_cny,(-1)*tt1.extax_fee_amount_cny,(-1)*tt1.tax_amount_cny,
       tt1.ccaltype,tt1.certificateno,v_confirmation_code,tt1.inquery_date,tt1.main_id,tt1.id,'F',tt1.crct_no
from udm_p_thirdcomm_fee_Archive tt1,udm_policy_main tt3
where tt1.policy_no=tt3.policy_no
  and tt3.endor_no=v_endor_no
  and tt1.main_id = p.id;

insert into UDM_P_THIRDSALES_FEEtemp(
    id,main_id, second_id, policy_no,endor_no,crct_no,
    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
    hedge_thirdid,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalefeeid.Nextval, 12, '0'),v_newMainID, tt1.second_id, tt3.policy_no,tt3.endor_no,tt1.crct_no,
       tt1.fee_type,tt1.operator_code,tt1.fee_rate,(-1)*tt1.fee_amount,(-1)*tt1.extax_fee_amount,
       (-1)*tt1.tax_amount,tt1.currency,tt1.tax_rate,(-1)*tt1.fee_amount_cny,(-1)*tt1.extax_fee_amount_cny,
       (-1)*tt1.tax_amount_cny,tt1.ccaltype,tt1.is_fixed_rate,tt1.main_id,
       tt1.id,'F'
from  udm_p_thirdsales_fee_Archive tt1,udm_policy_main tt3
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt1.main_id = p.id;

insert into UDM_P_THIRD_SALARYTEMP(
    id,main_id,second_id,policy_no ,endor_no,
    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
    ccaltype,hedge_sencondid,hedge_thirdid,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalaryid.Nextval, 12, '0'),v_newMainID,tt1.second_id,tt3.policy_no ,tt3.endor_no,
       tt1.crct_no,tt1.operator_code,tt1.fee_rate,(-1)*tt1.fee_amount,(-1)*tt1.fee_amount_cny,
       tt1.ccaltype,tt1.main_id,tt1.id,'F'
from UDM_P_THIRD_SALARY_Archive tt1,udm_policy_main tt3
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt1.main_id = p.id;
end loop;
            --对冲2：先冲负，再冲正，总金额为0;特殊，费用批改不应该包括其他对冲场景
        elsif (length(v_updatedfield)>3 and v_endor_type!='1010') or v_endor_type ='1022' or v_endor_type ='1023' THEN
            FOR p IN (SELECT tpremium1.id,tpremium1.kind_sn,tpremium1.kind_code,tpremium1.installment_sn FROM UDM_P_THIRDPREMIUM tpremium1 WHERE TPREMIUM1.policy_no=v_policy_no
               AND NOT EXISTS (SELECT 1 FROM UDM_P_THIRDPREMIUM tpremium2 WHERE tpremium2.hedge_thirdid=tpremium1.id
                    AND tpremium2.hedge_thirdid IS NOT null) AND tpremium1.hedge_thirdid IS NULL )LOOP

select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_TPremiumid.Nextval, 12, '0') into v_newMainID from dual;
select earned_startdate,earned_enddate into v_new_earned_startdate, v_new_earned_enddate from UDM_POLICY_COVERAGE where policy_no=v_policy_no and endor_no=v_endor_no and rownum=1;

insert into UDM_P_THIRDPREMIUMTEMP
(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,
 endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
 risk_code ,start_date,end_date,issue_date,uw_date ,
 transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
 sale_system ,init_policy_no,inception_date,renewed_policy_no ,
 business_source ,business_type ,appli_code,
 nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,insuredname,is_multi_insureds ,operator_code ,
 guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,
 is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
 debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,crct_no,installment_sn,
 installment_times ,payer_code,payer_name,coins_code,coins_name,
 coins_share ,coins_fee_rate,coins_type,coins_flag,
 joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,
 joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,
 treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
 k_end_date,k_valid_date,k_valid_date_old,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,
 suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
 premium_amount,premium_currency,premium_exrate,premium_amount_cny,vat_flag,taxfree_flag,tax_rate,
 extax_premium_amount,tax_amount,extax_premium_amount_cny,tax_amount_cny,
 totalpremium_amount,totalextax_premium_amount,totaltax_amount,
 totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,REFLAG,
 PAY_START_DATE,SUBJECT_NAME,policy_from,business_unit,warranty_period,pre_crctno
    ,producer_type, producer_code, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,accperiod_new,iscbc,isgiftinsurance)

select
    v_newMainID,tt3.id,tt3.sys_source,tt1.proposal_no,tt1.policy_no ,tt1.declaration_no,
    tt3.endorApply_no,tt3.endor_no,tt3.endor_sn,tt3.endor_type,tt1.cancel_reason ,tt1.issue_company ,
    tt1.risk_code ,tt1.start_date,tt1.end_date,tt1.issue_date,tt3.uw_date ,
    tt3.transaction_date,case when instr(v_updatedfield,'P005')>0 then tt1.valid_date else tt3.valid_date end,tt1.renew_flag,case when instr(v_updatedfield,'P005')>0 then tt1.statutory_accounting_period else greatest(tt3.statutory_accounting_period,tt3.uw_date) END, tt3.us_accounting_period,
    tt1.sale_system ,tt1.init_policy_no,tt1.inception_date,tt1.renewed_policy_no ,
    tt1.business_source ,tt1.business_type ,tt1.appli_code,
    tt1.nationalitycode,tt1.enterprisenature,tt1.htrelationship,tt1.circdomesticind,tt1.appliename,
    tt1.insuredname,tt1.is_multi_insureds ,tt1.operator_code ,
    tt1.guarantee_days,tt1.upc_flag,tt1.domestic_flag ,tt1.internet_flag ,tt1.policy_type ,
    tt1.is_prepaid,tt1.is_oversea,tt1.is_renewable,tt1.policy_renewalbe_no ,tt1.business_year ,
    tt1.debitnote_no,tt1.dm_flag ,tt1.hisdata_flag,tt1.modifydata_flag ,tt1.crct_no||'A',tt1.installment_sn,
    tt1.installment_times ,tt1.payer_code,tt1.payer_name,coins_code,tt1.coins_name,
    tt1.coins_share ,tt1.coins_fee_rate,tt1.coins_type,tt1.coins_flag,
    tt1.joins_code,tt1.joins_name,tt1.joins_share ,tt1.joins_policy_no ,tt1.joins_endor_no,tt1.joins_type,tt1.joins_flag,tt1.kind_sn ,tt1.kind_code ,tt1.kind_name ,tt1.kind_type ,
    tt1.treaty_type ,tt1.ht_treaty_share ,tt1.treaty_group,tt1.k_start_date,
    tt1.k_end_date,tt3.valid_date,tt1.k_valid_date,(-1)*suminsured_amount ,(-1)*tt1.deductible_amount ,tt1.suminsured_currency ,tt1.suminsured_exrate ,
    (-1)*tt1.suminsured_amount_cny ,(-1)*tt1.deductible_amount_cny ,tt1.deductible_rate ,
    (-1)*tt1.premium_amount,tt1.premium_currency,tt1.premium_exrate,(-1)*tt1.premium_amount_cny,tt1.vat_flag,tt1.taxfree_flag,tt1.tax_rate,
    (-1)*tt1.extax_premium_amount,(-1)*tt1.tax_amount,(-1)*tt1.extax_premium_amount_cny,(-1)*tt1.tax_amount_cny,
    0,0,0,
    0,0,0,tt1.second_main_id,tt1.id,'F',
    tt1.pay_start_date,tt1.subject_name,tt1.policy_from,tt1.business_unit,tt1.warranty_period,tt1.crct_no
        ,tt1.producer_type, tt1.producer_code,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_startdate --火险--有保费变动且更改保险期间	新单赚取起期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_startdate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）	旧单赚取起期
        else tt1.earned_startdate --其余场景（非火险以外的其余对冲场景）	旧单赚取起期
        end,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_enddate --火险--有保费变动且更改保险期间 新单赚取止期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_enddate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑） 旧单赚取止期
        else tt1.earned_enddate --其余场景（非火险以外的其余对冲场景） 旧单赚取止期
        end,
    v_line_of_business,v_lineofbusiness_lv4,
    case when instr(v_updatedfield,'P005')>0 then greatest(tt1.start_date,tt3.uw_date,tt1.valid_date) else greatest(tt3.statutory_accounting_period,tt3.valid_date) END,
    tt1.iscbc,tt1.isgiftinsurance
from UDM_P_THIRDPREMIUM tt1,udm_policy_main tt3
where tt1.policy_no=tt3.policy_no and tt3.endor_no= v_endor_no
  AND tt1.id = p.id;

insert into UDM_P_THIRDCOMM_FEEtemp(id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
                                    operator_code,agreement_code,producer_type,producer_code,producer_name,is_main_producer,producer_tax_rate,fee_rate,
                                    fee_amount,extax_fee_amount, tax_amount,currency,exrate,fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
                                    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0'),v_newMainID,tt1.second_id,tt3.policy_no,tt3.endor_no,tt3.endor_sn,
       tt1.operator_code,tt1.agreement_code,tt1.producer_type,tt1.producer_code,tt1.producer_name,tt1.is_main_producer,tt1.producer_tax_rate,tt1.fee_rate,
       (-1)*tt1.fee_amount,(-1)*tt1.extax_fee_amount, (-1)*tt1.tax_amount,tt1.currency,tt1.exrate,(-1)*tt1.fee_amount_cny,(-1)*tt1.extax_fee_amount_cny,(-1)*tt1.tax_amount_cny,
       tt1.ccaltype,tt1.certificateno,v_confirmation_code,tt1.inquery_date,tt1.main_id,tt1.id,'F'
from UDM_P_THIRDCOMM_FEE tt1,udm_policy_main tt3
where tt1.policy_no=tt3.policy_no
  and tt3.endor_no=v_endor_no
  and tt1.main_id = p.id;

insert into UDM_P_THIRDSALES_FEEtemp(
    id,main_id, second_id, policy_no,endor_no,crct_no,
    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
    hedge_thirdid,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalefeeid.Nextval, 12, '0'),v_newMainID, tt1.second_id, tt3.policy_no,tt3.endor_no,tt1.crct_no,
       tt1.fee_type,tt1.operator_code,tt1.fee_rate,(-1)*tt1.fee_amount,(-1)*tt1.extax_fee_amount,
       (-1)*tt1.tax_amount,tt1.currency,tt1.tax_rate,(-1)*tt1.fee_amount_cny,(-1)*tt1.extax_fee_amount_cny,
       (-1)*tt1.tax_amount_cny,tt1.ccaltype,tt1.is_fixed_rate,tt1.main_id,
       tt1.id,'F'
from  UDM_P_THIRDSALES_FEE tt1,udm_policy_main tt3
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt1.main_id = p.id;

insert into UDM_P_THIRD_SALARYTEMP(
    id,main_id,second_id,policy_no ,endor_no,
    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
    ccaltype,hedge_sencondid,hedge_thirdid,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalaryid.Nextval, 12, '0'),v_newMainID,tt1.second_id,tt3.policy_no ,tt3.endor_no,
       tt1.crct_no,tt1.operator_code,tt1.fee_rate,(-1)*tt1.fee_amount,(-1)*tt1.fee_amount_cny,
       tt1.ccaltype,tt1.main_id,tt1.id,'F'
from UDM_P_THIRD_SALARY tt1,udm_policy_main tt3
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt1.main_id = p.id;

--按照新批单基础信息增加被对冲数据的正数
--对于车险有险别删除后又新增的情况，这种被删除的险别会一直存在，所以不用倒历史，用最新的即可
select count(1) into v_count from udm_policy_coverage mm where mm.endor_no=v_endor_no
                                                           and nvl(mm.kind_sn,1)=nvl(p.kind_sn,1) and mm.kind_code=p.kind_code;
if v_count>0 then
select mm.treaty_type,mm.ht_treaty_share,mm.treaty_group,mm.start_date,mm.end_date,mm.valid_date,mm.subject_name
into v_treaty_type,v_ht_treaty_share,v_treaty_group,v_k_start_date,v_k_end_date,v_k_valid_date,v_subject_name
from udm_policy_coverage mm where mm.endor_no=v_endor_no and nvl(mm.kind_sn,1)=nvl(p.kind_sn,1)
                              and mm.kind_code=p.kind_code;
else --批单找不到该责任 则取上一个正数保费的批单的值写入，见商险三层问题30
select mm.treaty_type,mm.ht_treaty_share,mm.treaty_group,mm.start_date,mm.end_date,mm.valid_date,mm.subject_name
into v_treaty_type,v_ht_treaty_share,v_treaty_group,v_k_start_date,v_k_end_date,v_k_valid_date,v_subject_name
from udm_policy_coverage mm where mm.policy_no=v_policy_no and nvl(mm.kind_sn,1)=nvl(p.kind_sn,1)
                              and mm.kind_code=p.kind_code order by mm.timestamp desc FETCH FIRST 1 ROWS ONLY;
end if;

select max(producer_type),max(producer_code) into v_producer_type, v_producer_code
from udm_policy_comm_fee  where endor_no=v_endor_no and is_main_producer = '1';

select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_TPremiumid.Nextval, 12, '0') into v_newMainID2 from dual;

select earned_startdate,earned_enddate into v_new_earned_startdate, v_new_earned_enddate from UDM_POLICY_COVERAGE where policy_no=v_policy_no and endor_no=v_endor_no and rownum=1;

insert into UDM_P_THIRDPREMIUMTEMP
(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,
 endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
 risk_code ,start_date,end_date,issue_date,uw_date ,
 transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
 sale_system ,init_policy_no,inception_date,renewed_policy_no ,
 business_source ,business_type ,appli_code,
 nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,
 insuredname,is_multi_insureds ,operator_code ,
 guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,
 is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
 debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,crct_no,installment_sn,
 installment_times ,payer_code,payer_name,coins_code,coins_name,
 coins_share ,coins_fee_rate,coins_type,coins_flag,
 joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,
 treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
 k_end_date,k_valid_date,k_valid_date_old,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,
 suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
 premium_amount,premium_currency,premium_exrate,premium_amount_cny,vat_flag,taxfree_flag,tax_rate,
 extax_premium_amount,tax_amount,extax_premium_amount_cny,tax_amount_cny,
 totalpremium_amount,totalextax_premium_amount,totaltax_amount,
 totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,REFLAG,
 PAY_START_DATE,SUBJECT_NAME,policy_from,business_unit,warranty_period,src_sencondid,src_crctno
    ,producer_type, producer_code, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,accperiod_new,iscbc,isgiftinsurance)

select
    v_newMainID2,tt3.id,tt3.sys_source,tt3.proposal_no,tt3.policy_no ,tt3.declaration_no,
    tt3.endorApply_no,tt3.endor_no,tt3.endor_sn,tt3.endor_type,tt3.cancel_reason ,tt3.issue_company ,
    tt3.risk_code ,tt3.start_date,tt3.end_date,tt3.issue_date,tt3.uw_date ,
    tt3.transaction_date,tt3.valid_date,tt3.renew_flag,tt3.statutory_accounting_period, tt3.us_accounting_period,
    tt3.sale_system ,tt3.init_policy_no,tt3.inception_date,tt3.renewed_policy_no ,
    tt3.business_source ,tt3.business_type ,tt3.appli_code,
    tt4.nationalitycode,tt4.enterprisenature,tt4.htrelationship,tt4.circdomesticind,tt4.appliename,
    g.insuredname,tt3.is_multi_insureds ,tt3.operator_code ,
    tt3.guarantee_days,tt3.upc_flag,tt3.domestic_flag ,tt3.internet_flag ,tt3.policy_type ,
    tt3.is_prepaid,tt3.is_oversea,tt3.is_renewable,tt3.policy_renewalbe_no ,tt3.business_year ,
    tt1.debitnote_no,tt3.dm_flag ,tt3.hisdata_flag,tt3.modifydata_flag ,tt1.crct_no||'B',tt1.installment_sn,
    tt1.installment_times ,tt1.payer_code,tt1.payer_name,coins_code,tt1.coins_name,
    tt1.coins_share ,tt1.coins_fee_rate,tt1.coins_type,tt1.coins_flag,
    tt1.joins_code,tt1.joins_name,tt1.joins_share ,tt1.joins_policy_no ,tt1.joins_endor_no,tt1.joins_type,tt1.joins_flag,tt1.kind_sn ,tt1.kind_code ,tt1.kind_name ,tt1.kind_type ,
    v_treaty_type ,v_ht_treaty_share ,v_treaty_group,v_k_start_date,
    v_k_end_date,v_k_valid_date,tt1.k_valid_date,tt1.suminsured_amount ,tt1.deductible_amount ,tt1.suminsured_currency ,tt1.suminsured_exrate ,
    tt1.suminsured_amount_cny ,tt1.deductible_amount_cny ,tt1.deductible_rate ,
    tt1.premium_amount,tt1.premium_currency,tt1.premium_exrate,tt1.premium_amount_cny,tt1.vat_flag,tt1.taxfree_flag,tt1.tax_rate,
    tt1.extax_premium_amount,tt1.tax_amount,tt1.extax_premium_amount_cny,tt1.tax_amount_cny,
    /*tt1.totalpremium_amount,tt1.totalextax_premium_amount,tt1.totaltax_amount,
    tt1.totalpremium_amount_cny,tt1.totalextax_premium_amount_cny,tt1.totaltax_amount_cny,*/
    0,0,0,0,0,0,
    null,null,'Z',
    tt1.pay_start_date,v_subject_name,tt3.policy_from,tt3.business_unit,tt3.warranty_period,tt1.second_main_id,tt1.crct_no
        ,v_producer_type, v_producer_code,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_startdate --火险--有保费变动且更改保险期间	新单赚取起期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_startdate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）	旧单赚取起期
        else v_new_earned_startdate --其余场景（非火险以外的其余对冲场景）	旧单赚取起期
        end,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_enddate --火险--有保费变动且更改保险期间 新单赚取止期
        when v_earned_startdate_enddate_hedge_way = '2' then v_new_earned_enddate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑） 旧单赚取止期
        else v_new_earned_enddate --其余场景（非火险以外的其余对冲场景） 旧单赚取止期
        end,
    v_line_of_business,v_lineofbusiness_lv4,tt1.accperiod_new,tt3.iscbc,tt3.isgiftinsurance
from UDM_P_THIRDPREMIUM tt1,udm_policy_main tt3,UDM_POLICY_HOLDER tt4
   ,(select policy_no,ENDOR_NO,insuredname from udm_policy_insured where
    policy_no=v_policy_no and endor_no = v_endor_no and rownum=1) g
where tt1.policy_no=tt3.policy_no and tt3.endor_no= v_endor_no
  and tt3.endor_no = tt4.endor_no and tt3.endor_no = g.endor_no
  and tt1.id=p.id;

/**
兼容代理被批退后最新批单没有代理人的情况，需要查历史最新一次的，业务员字段使用主表最新的
*/
select count(1) into v_count
from UDM_P_THIRDCOMM_FEE tt1,udm_policy_comm_fee tt4
where tt1.policy_no=tt4.policy_no
  and tt4.endor_no = v_endor_no
  and tt4.producer_code = tt1.producer_code
  and tt1.main_id = p.id;
if v_count>0 then
                        v_commfee_endorno := v_endor_no;
else
select count(1) into v_count
from UDM_P_THIRDCOMM_FEE tt1,udm_policy_comm_fee tt4
where tt1.policy_no=tt4.policy_no
  and tt4.policy_no = v_policy_no
  and tt4.producer_code = tt1.producer_code
  and tt1.main_id = p.id;
if v_count>0 then
select aa.endor_no into v_commfee_endorno from (select tt4.endor_no
                                                from UDM_P_THIRDCOMM_FEE tt1,udm_policy_comm_fee tt4
                                                where tt1.policy_no=tt4.policy_no
                                                  and tt4.policy_no = v_policy_no
                                                  and tt4.producer_code = tt1.producer_code
                                                  and tt1.main_id = p.id order by tt4.id desc) aa where rownum=1;
end if;
end if;

insert into UDM_P_THIRDCOMM_FEEtemp(id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
                                    operator_code,agreement_code,producer_type,producer_code,producer_name,is_main_producer,producer_tax_rate,fee_rate,
                                    fee_amount,extax_fee_amount, tax_amount,currency,exrate,fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
                                    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0'),v_newMainID2,tt4.id,tt3.policy_no,tt3.endor_no,tt3.endor_sn,
       tt3.operator_code,tt4.agreement_code,tt4.producer_type,tt1.producer_code,tt4.producer_name,tt4.is_main_producer,tt4.producer_tax_rate,tt4.fee_rate,
       tt1.fee_amount,tt1.extax_fee_amount, tt1.tax_amount,tt1.currency,tt1.exrate,tt1.fee_amount_cny,tt1.extax_fee_amount_cny,tt1.tax_amount_cny,
       tt4.ccaltype,tt4.certificateno,tt4.confirmation_code,tt4.inquery_date,null,null,'Z'
from UDM_P_THIRDCOMM_FEE tt1,udm_policy_main tt3,udm_policy_comm_fee tt4
where tt1.policy_no=tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt3.policy_no = tt4.policy_no
  and tt4.endor_no = v_commfee_endorno
  and tt4.producer_code = tt1.producer_code
  and tt1.main_id = p.id;

/**
兼容某种费用类型被批退后最新批单没有此费用类型的情况，需要查历史最新一次的，业务员字段使用主表最新的
*/
select count(1) into v_count
from UDM_P_THIRDSALES_FEE tt1,udm_policy_sales_fee tt4
where tt1.policy_no = tt4.policy_no
  and tt1.fee_type = tt4.fee_type
  and tt1.main_id = p.id
  and tt4.endor_no = v_endor_no;
if v_count>0 then
                        v_salefee_endorno := v_endor_no;
else
select count(1) into v_count
from UDM_P_THIRDSALES_FEE tt1,udm_policy_sales_fee tt4
where tt1.policy_no = tt4.policy_no
  and tt1.fee_type = tt4.fee_type
  and tt1.main_id = p.id
  and tt4.policy_no = v_policy_no;
if v_count>0 then
select aa.endor_no into v_salefee_endorno from (select tt4.endor_no
                                                from UDM_P_THIRDSALES_FEE tt1,udm_policy_sales_fee tt4
                                                where tt1.policy_no = tt4.policy_no and tt1.fee_type = tt4.fee_type and tt1.main_id = p.id
                                                  and tt4.policy_no = v_policy_no order by tt4.id desc) aa where rownum=1;
end if;
end if;
insert into UDM_P_THIRDSALES_FEEtemp(
    id,main_id, second_id, policy_no,endor_no,crct_no,
    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
    hedge_thirdid,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalefeeid.Nextval, 12, '0'),v_newMainID2,tt4.id, tt3.policy_no,tt3.endor_no,tt4.crct_no,
       tt4.fee_type,tt3.operator_code,tt4.fee_rate,tt1.fee_amount,tt1.extax_fee_amount,
       tt1.tax_amount,tt4.currency,tt4.tax_rate,tt1.fee_amount_cny,tt1.extax_fee_amount_cny,
       tt1.tax_amount_cny,tt4.ccaltype,tt4.is_fixed_rate,null,
       null,'Z'
from UDM_P_THIRDSALES_FEE tt1,udm_policy_main tt3,udm_policy_sales_fee tt4
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt4.policy_no = tt3.policy_no
  and tt4.endor_no = v_salefee_endorno
  and tt1.fee_type = tt4.fee_type
  and tt1.main_id = p.id;

select count(1) into v_count from udm_policy_salary a where a.endor_no=v_endor_no;
if v_count>0 then
                        v_salary_endorno := v_endor_no;
else
select count(1) into v_count from udm_policy_salary a where a.policy_no=v_policy_no;
if v_count>0 then
select aa.endor_no into v_salary_endorno from (select a.endor_no from udm_policy_salary a,udm_policy_main b
                                               where a.policy_no=b.policy_no and a.policy_no=v_policy_no order by b.endor_sn desc) aa where rownum=1;
end if;
end if;
insert into UDM_P_THIRD_SALARYTEMP(
    id,main_id,second_id,policy_no ,endor_no,
    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
    ccaltype,hedge_sencondid,hedge_thirdid,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalaryid.Nextval, 12, '0'),v_newMainID2,tt4.id,tt3.policy_no ,tt3.endor_no,
       tt4.crct_no,tt3.operator_code,tt4.fee_rate,tt1.fee_amount,tt1.fee_amount_cny,
       tt4.ccaltype,null,null,'Z'
from UDM_P_THIRD_SALARY tt1,udm_policy_main tt3,udm_policy_salary tt4
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt4.policy_no = tt3.policy_no
  and tt4.endor_no = v_salary_endorno
  and tt1.main_id = p.id;
end LOOP;

         --处理归档表
FOR p IN (SELECT tpremium1.id,tpremium1.kind_sn,tpremium1.kind_code,tpremium1.installment_sn FROM UDM_P_THIRDPREMIUM_Archive tpremium1 WHERE TPREMIUM1.policy_no=v_policy_no
              AND NOT EXISTS (SELECT 1 FROM UDM_P_THIRDPREMIUM_Archive tpremium2 WHERE tpremium2.hedge_thirdid=tpremium1.id
              AND tpremium2.hedge_thirdid IS NOT null) AND tpremium1.hedge_thirdid IS NULL )LOOP

select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_TPremiumid.Nextval, 12, '0') into v_newMainID from dual;

select earned_startdate,earned_enddate into v_new_earned_startdate, v_new_earned_enddate from UDM_POLICY_COVERAGE where policy_no=v_policy_no and endor_no=v_endor_no and rownum=1;

insert into UDM_P_THIRDPREMIUMTEMP
(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,
 endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
 risk_code ,start_date,end_date,issue_date,uw_date ,
 transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
 sale_system ,init_policy_no,inception_date,renewed_policy_no ,
 business_source ,business_type ,appli_code,
 nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,insuredname,is_multi_insureds ,operator_code ,
 guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,
 is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
 debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,crct_no,installment_sn,
 installment_times ,payer_code,payer_name,coins_code,coins_name,
 coins_share ,coins_fee_rate,coins_type,coins_flag,
 joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,
 joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,
 treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
 k_end_date,k_valid_date,k_valid_date_old,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,
 suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
 premium_amount,premium_currency,premium_exrate,premium_amount_cny,vat_flag,taxfree_flag,tax_rate,
 extax_premium_amount,tax_amount,extax_premium_amount_cny,tax_amount_cny,
 totalpremium_amount,totalextax_premium_amount,totaltax_amount,
 totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,REFLAG,
 PAY_START_DATE,SUBJECT_NAME,policy_from,business_unit,warranty_period,pre_crctno
    ,producer_type, producer_code, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,accperiod_new,iscbc,isgiftinsurance)

select
    v_newMainID,tt3.id,tt3.sys_source,tt1.proposal_no,tt1.policy_no ,tt1.declaration_no,
    tt3.endorApply_no,tt3.endor_no,tt3.endor_sn,tt3.endor_type,tt1.cancel_reason ,tt1.issue_company ,
    tt1.risk_code ,tt1.start_date,tt1.end_date,tt1.issue_date,tt3.uw_date ,
    tt3.transaction_date,case when instr(v_updatedfield,'P005')>0 then tt1.valid_date else tt3.valid_date end,tt1.renew_flag,case when instr(v_updatedfield,'P005')>0 then tt1.statutory_accounting_period else greatest(tt3.statutory_accounting_period,tt3.uw_date) END, tt3.us_accounting_period,
    tt1.sale_system ,tt1.init_policy_no,tt1.inception_date,tt1.renewed_policy_no ,
    tt1.business_source ,tt1.business_type ,tt1.appli_code,
    tt1.nationalitycode,tt1.enterprisenature,tt1.htrelationship,tt1.circdomesticind,tt1.appliename,
    tt1.insuredname,tt1.is_multi_insureds ,tt1.operator_code ,
    tt1.guarantee_days,tt1.upc_flag,tt1.domestic_flag ,tt1.internet_flag ,tt1.policy_type ,
    tt1.is_prepaid,tt1.is_oversea,tt1.is_renewable,tt1.policy_renewalbe_no ,tt1.business_year ,
    tt1.debitnote_no,tt1.dm_flag ,tt1.hisdata_flag,tt1.modifydata_flag ,tt1.crct_no||'A',tt1.installment_sn,
    tt1.installment_times ,tt1.payer_code,tt1.payer_name,coins_code,tt1.coins_name,
    tt1.coins_share ,tt1.coins_fee_rate,tt1.coins_type,tt1.coins_flag,
    tt1.joins_code,tt1.joins_name,tt1.joins_share ,tt1.joins_policy_no ,tt1.joins_endor_no,tt1.joins_type,tt1.joins_flag,tt1.kind_sn ,tt1.kind_code ,tt1.kind_name ,tt1.kind_type ,
    tt1.treaty_type ,tt1.ht_treaty_share ,tt1.treaty_group,tt1.k_start_date,
    tt1.k_end_date,tt3.valid_date,tt1.k_valid_date,(-1)*suminsured_amount ,(-1)*tt1.deductible_amount ,tt1.suminsured_currency ,tt1.suminsured_exrate ,
    (-1)*tt1.suminsured_amount_cny ,(-1)*tt1.deductible_amount_cny ,tt1.deductible_rate ,
    (-1)*tt1.premium_amount,tt1.premium_currency,tt1.premium_exrate,(-1)*tt1.premium_amount_cny,tt1.vat_flag,tt1.taxfree_flag,tt1.tax_rate,
    (-1)*tt1.extax_premium_amount,(-1)*tt1.tax_amount,(-1)*tt1.extax_premium_amount_cny,(-1)*tt1.tax_amount_cny,
    0,0,0,
    0,0,0,tt1.second_main_id,tt1.id,'F',
    tt1.pay_start_date,tt1.subject_name,tt1.policy_from,tt1.business_unit,tt1.warranty_period,tt1.crct_no
        ,tt1.producer_type, tt1.producer_code,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_startdate --火险--有保费变动且更改保险期间	新单赚取起期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_startdate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）	旧单赚取起期
        else v_new_earned_startdate --其余场景（非火险以外的其余对冲场景）	旧单赚取起期
        end,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_enddate --火险--有保费变动且更改保险期间 新单赚取止期
        when v_earned_startdate_enddate_hedge_way = '2' then v_new_earned_enddate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑） 旧单赚取止期
        else v_new_earned_enddate --其余场景（非火险以外的其余对冲场景） 旧单赚取止期
        end,
    v_line_of_business,v_lineofbusiness_lv4,
    case when instr(v_updatedfield,'P005')>0 then greatest(tt1.start_date,tt3.uw_date,tt1.valid_date) else greatest(tt3.statutory_accounting_period,tt3.valid_date) END,
    tt1.iscbc,tt1.isgiftinsurance
from UDM_P_THIRDPREMIUM_Archive tt1,udm_policy_main tt3
where tt1.policy_no=tt3.policy_no and tt3.endor_no= v_endor_no
  AND tt1.id = p.id;

insert into UDM_P_THIRDCOMM_FEEtemp(id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
                                    operator_code,agreement_code,producer_type,producer_code,producer_name,is_main_producer,producer_tax_rate,fee_rate,
                                    fee_amount,extax_fee_amount, tax_amount,currency,exrate,fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
                                    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0'),v_newMainID,tt1.second_id,tt3.policy_no,tt3.endor_no,tt3.endor_sn,
       tt1.operator_code,tt1.agreement_code,tt1.producer_type,tt1.producer_code,tt1.producer_name,tt1.is_main_producer,tt1.producer_tax_rate,tt1.fee_rate,
       (-1)*tt1.fee_amount,(-1)*tt1.extax_fee_amount, (-1)*tt1.tax_amount,tt1.currency,tt1.exrate,(-1)*tt1.fee_amount_cny,(-1)*tt1.extax_fee_amount_cny,(-1)*tt1.tax_amount_cny,
       tt1.ccaltype,tt1.certificateno,v_confirmation_code,tt1.inquery_date,tt1.main_id,tt1.id,'F'
from udm_p_thirdcomm_fee_Archive tt1,udm_policy_main tt3
where tt1.policy_no=tt3.policy_no
  and tt3.endor_no=v_endor_no
  and tt1.main_id = p.id;

insert into UDM_P_THIRDSALES_FEEtemp(
    id,main_id, second_id, policy_no,endor_no,crct_no,
    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
    hedge_thirdid,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalefeeid.Nextval, 12, '0'),v_newMainID, tt1.second_id, tt3.policy_no,tt3.endor_no,tt1.crct_no,
       tt1.fee_type,tt1.operator_code,tt1.fee_rate,(-1)*tt1.fee_amount,(-1)*tt1.extax_fee_amount,
       (-1)*tt1.tax_amount,tt1.currency,tt1.tax_rate,(-1)*tt1.fee_amount_cny,(-1)*tt1.extax_fee_amount_cny,
       (-1)*tt1.tax_amount_cny,tt1.ccaltype,tt1.is_fixed_rate,tt1.main_id,
       tt1.id,'F'
from  udm_p_thirdsales_fee_Archive tt1,udm_policy_main tt3
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt1.main_id = p.id;

insert into UDM_P_THIRD_SALARYTEMP(
    id,main_id,second_id,policy_no ,endor_no,
    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
    ccaltype,hedge_sencondid,hedge_thirdid,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalaryid.Nextval, 12, '0'),v_newMainID,tt1.second_id,tt3.policy_no ,tt3.endor_no,
       tt1.crct_no,tt1.operator_code,tt1.fee_rate,(-1)*tt1.fee_amount,(-1)*tt1.fee_amount_cny,
       tt1.ccaltype,tt1.main_id,tt1.id,'F'
from UDM_P_THIRD_SALARY_Archive tt1,udm_policy_main tt3
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt1.main_id = p.id;

--按照新批单基础信息增加被对冲数据的正数
--对于车险有险别删除后又新增的情况，这种被删除的险别会一直存在，所以不用倒历史，用最新的即可
select count(1) into v_count from udm_policy_coverage mm where mm.endor_no=v_endor_no
                                                           and nvl(mm.kind_sn,1)=nvl(p.kind_sn,1) and mm.kind_code=p.kind_code;
if v_count>0 then
select mm.treaty_type,mm.ht_treaty_share,mm.treaty_group,mm.start_date,mm.end_date,mm.valid_date,mm.subject_name
into v_treaty_type,v_ht_treaty_share,v_treaty_group,v_k_start_date,v_k_end_date,v_k_valid_date,v_subject_name
from udm_policy_coverage mm where mm.endor_no=v_endor_no and nvl(mm.kind_sn,1)=nvl(p.kind_sn,1)
                              and mm.kind_code=p.kind_code;
end if;

select max(producer_type),max(producer_code) into v_producer_type, v_producer_code
from udm_policy_comm_fee  where endor_no=v_endor_no and is_main_producer = '1';

select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_TPremiumid.Nextval, 12, '0') into v_newMainID2 from dual;

select earned_startdate,earned_enddate into v_new_earned_startdate, v_new_earned_enddate from UDM_POLICY_COVERAGE where policy_no=v_policy_no and endor_no=v_endor_no and rownum=1;

insert into UDM_P_THIRDPREMIUMTEMP
(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,
 endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
 risk_code ,start_date,end_date,issue_date,uw_date ,
 transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
 sale_system ,init_policy_no,inception_date,renewed_policy_no ,
 business_source ,business_type ,appli_code,
 nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,
 insuredname,is_multi_insureds ,operator_code ,
 guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,
 is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
 debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,crct_no,installment_sn,
 installment_times ,payer_code,payer_name,coins_code,coins_name,
 coins_share ,coins_fee_rate,coins_type,coins_flag,
 joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,
 treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
 k_end_date,k_valid_date,k_valid_date_old,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,
 suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
 premium_amount,premium_currency,premium_exrate,premium_amount_cny,vat_flag,taxfree_flag,tax_rate,
 extax_premium_amount,tax_amount,extax_premium_amount_cny,tax_amount_cny,
 totalpremium_amount,totalextax_premium_amount,totaltax_amount,
 totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,REFLAG,
 PAY_START_DATE,SUBJECT_NAME,policy_from,business_unit,warranty_period,src_sencondid,src_crctno
    ,producer_type, producer_code, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,accperiod_new,iscbc,isgiftinsurance)

select
    v_newMainID2,tt3.id,tt3.sys_source,tt3.proposal_no,tt3.policy_no ,tt3.declaration_no,
    tt3.endorApply_no,tt3.endor_no,tt3.endor_sn,tt3.endor_type,tt3.cancel_reason ,tt3.issue_company ,
    tt3.risk_code ,tt3.start_date,tt3.end_date,tt3.issue_date,tt3.uw_date ,
    tt3.transaction_date,tt3.valid_date,tt3.renew_flag,tt3.statutory_accounting_period, tt3.us_accounting_period,
    tt3.sale_system ,tt3.init_policy_no,tt3.inception_date,tt3.renewed_policy_no ,
    tt3.business_source ,tt3.business_type ,tt3.appli_code,
    tt4.nationalitycode,tt4.enterprisenature,tt4.htrelationship,tt4.circdomesticind,tt4.appliename,
    g.insuredname,tt3.is_multi_insureds ,tt3.operator_code ,
    tt3.guarantee_days,tt3.upc_flag,tt3.domestic_flag ,tt3.internet_flag ,tt3.policy_type ,
    tt3.is_prepaid,tt3.is_oversea,tt3.is_renewable,tt3.policy_renewalbe_no ,tt3.business_year ,
    tt1.debitnote_no,tt3.dm_flag ,tt3.hisdata_flag,tt3.modifydata_flag ,tt1.crct_no||'B',tt1.installment_sn,
    tt1.installment_times ,tt1.payer_code,tt1.payer_name,coins_code,tt1.coins_name,
    tt1.coins_share ,tt1.coins_fee_rate,tt1.coins_type,tt1.coins_flag,
    tt1.joins_code,tt1.joins_name,tt1.joins_share ,tt1.joins_policy_no ,tt1.joins_endor_no,tt1.joins_type,tt1.joins_flag,tt1.kind_sn ,tt1.kind_code ,tt1.kind_name ,tt1.kind_type ,
    v_treaty_type ,v_ht_treaty_share ,v_treaty_group,v_k_start_date,
    v_k_end_date,v_k_valid_date,tt1.k_valid_date,tt1.suminsured_amount ,tt1.deductible_amount ,tt1.suminsured_currency ,tt1.suminsured_exrate ,
    tt1.suminsured_amount_cny ,tt1.deductible_amount_cny ,tt1.deductible_rate ,
    tt1.premium_amount,tt1.premium_currency,tt1.premium_exrate,tt1.premium_amount_cny,tt1.vat_flag,tt1.taxfree_flag,tt1.tax_rate,
    tt1.extax_premium_amount,tt1.tax_amount,tt1.extax_premium_amount_cny,tt1.tax_amount_cny,
    /*tt1.totalpremium_amount,tt1.totalextax_premium_amount,tt1.totaltax_amount,
    tt1.totalpremium_amount_cny,tt1.totalextax_premium_amount_cny,tt1.totaltax_amount_cny,*/
    0,0,0,0,0,0,
    null,null,'Z',
    tt1.pay_start_date,v_subject_name,tt3.policy_from,tt3.business_unit,tt3.warranty_period,tt1.second_main_id,tt1.crct_no
        ,v_producer_type, v_producer_code,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_startdate --火险--有保费变动且更改保险期间	新单赚取起期
        when v_earned_startdate_enddate_hedge_way = '2' then tt1.earned_startdate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）	旧单赚取起期
        else v_new_earned_startdate --其余场景（非火险以外的其余对冲场景）	旧单赚取起期
        end,
    case
        when v_earned_startdate_enddate_hedge_way = '1' then v_new_earned_enddate --火险--有保费变动且更改保险期间 新单赚取止期
        when v_earned_startdate_enddate_hedge_way = '2' then v_new_earned_enddate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑） 旧单赚取止期
        else v_new_earned_enddate --其余场景（非火险以外的其余对冲场景） 旧单赚取止期
        end,
    v_line_of_business,v_lineofbusiness_lv4,tt1.accperiod_new,
    tt3.iscbc,tt3.isgiftinsurance
from UDM_P_THIRDPREMIUM_Archive tt1,udm_policy_main tt3,UDM_POLICY_HOLDER tt4
   ,(select policy_no,ENDOR_NO,insuredname from udm_policy_insured where
    policy_no=v_policy_no and endor_no = v_endor_no and rownum=1) g
where tt1.policy_no=tt3.policy_no and tt3.endor_no= v_endor_no
  and tt3.endor_no = tt4.endor_no and tt3.endor_no = g.endor_no
  and tt1.id=p.id;

/**
兼容代理被批退后最新批单没有代理人的情况，需要查历史最新一次的，业务员字段使用主表最新的
*/
select count(1) into v_count
from udm_p_thirdcomm_fee_Archive tt1,udm_policy_comm_fee tt4
where tt1.policy_no=tt4.policy_no
  and tt4.endor_no = v_endor_no
  and tt4.producer_code = tt1.producer_code
  and tt1.main_id = p.id;
if v_count>0 then
             v_commfee_endorno := v_endor_no;
else
select count(1) into v_count
from udm_p_thirdcomm_fee_Archive tt1,udm_policy_comm_fee tt4
where tt1.policy_no=tt4.policy_no
  and tt4.policy_no = v_policy_no
  and tt4.producer_code = tt1.producer_code
  and tt1.main_id = p.id;
if v_count>0 then
select aa.endor_no into v_commfee_endorno from (select tt4.endor_no
                                                from udm_p_thirdcomm_fee_Archive tt1,udm_policy_comm_fee tt4
                                                where tt1.policy_no=tt4.policy_no
                                                  and tt4.policy_no = v_policy_no
                                                  and tt4.producer_code = tt1.producer_code
                                                  and tt1.main_id = p.id order by tt4.id desc) aa where rownum=1;
end if;
end if;

insert into UDM_P_THIRDCOMM_FEEtemp(id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
                                    operator_code,agreement_code,producer_type,producer_code,producer_name,is_main_producer,producer_tax_rate,fee_rate,
                                    fee_amount,extax_fee_amount, tax_amount,currency,exrate,fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
                                    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0'),v_newMainID2,tt4.id,tt3.policy_no,tt3.endor_no,tt3.endor_sn,
       tt3.operator_code,tt4.agreement_code,tt4.producer_type,tt1.producer_code,tt4.producer_name,tt4.is_main_producer,tt4.producer_tax_rate,tt4.fee_rate,
       tt1.fee_amount,tt1.extax_fee_amount, tt1.tax_amount,tt4.currency,tt4.tax_rate,tt1.fee_amount_cny,tt1.extax_fee_amount_cny,tt1.tax_amount_cny,
       tt4.ccaltype,tt4.certificateno,tt4.confirmation_code,tt4.inquery_date,null,null,'Z'
from udm_p_thirdcomm_fee_Archive tt1,udm_policy_main tt3,udm_policy_comm_fee tt4
where tt1.policy_no=tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt3.policy_no = tt4.policy_no
  and tt4.endor_no = v_commfee_endorno
  and tt4.producer_code = tt1.producer_code
  and tt1.main_id = p.id;

/**
兼容某种费用类型被批退后最新批单没有此费用类型的情况，需要查历史最新一次的，业务员字段使用主表最新的
*/
select count(1) into v_count
from udm_p_thirdsales_fee_Archive tt1,udm_policy_sales_fee tt4
where tt1.policy_no = tt4.policy_no
  and tt1.fee_type = tt4.fee_type
  and tt1.main_id = p.id
  and tt4.endor_no = v_endor_no;
if v_count>0 then
             v_salefee_endorno := v_endor_no;
else
select count(1) into v_count
from udm_p_thirdsales_fee_Archive tt1,udm_policy_sales_fee tt4
where tt1.policy_no = tt4.policy_no
  and tt1.fee_type = tt4.fee_type
  and tt1.main_id = p.id
  and tt4.policy_no = v_policy_no;
if v_count>0 then
select aa.endor_no into v_salefee_endorno from (select tt4.endor_no
                                                from udm_p_thirdsales_fee_Archive tt1,udm_policy_sales_fee tt4
                                                where tt1.policy_no = tt4.policy_no and tt1.fee_type = tt4.fee_type and tt1.main_id = p.id
                                                  and tt4.policy_no = v_policy_no order by tt4.id desc) aa where rownum=1;
end if;
end if;
insert into UDM_P_THIRDSALES_FEEtemp(
    id,main_id, second_id, policy_no,endor_no,crct_no,
    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
    hedge_thirdid,HEDGE_FLAG)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalefeeid.Nextval, 12, '0'),v_newMainID2,tt4.id, tt3.policy_no,tt3.endor_no,tt4.crct_no,
       tt4.fee_type,tt3.operator_code,tt4.fee_rate,tt1.fee_amount,tt1.extax_fee_amount,
       tt1.tax_amount,tt4.currency,tt4.tax_rate,tt1.fee_amount_cny,tt1.extax_fee_amount_cny,
       tt1.tax_amount_cny,tt4.ccaltype,tt4.is_fixed_rate,null,
       null,'Z'
from udm_p_thirdsales_fee_Archive tt1,udm_policy_main tt3,udm_policy_sales_fee tt4
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt4.policy_no = tt3.policy_no
  and tt4.endor_no = v_salefee_endorno
  and tt1.fee_type = tt4.fee_type
  and tt1.main_id = p.id;

select count(1) into v_count from udm_policy_salary a where a.endor_no=v_endor_no;
if v_count>0 then
              v_salary_endorno := v_endor_no;
else
select count(1) into v_count from udm_policy_salary a where a.policy_no=v_policy_no;
if v_count>0 then
select aa.endor_no into v_salary_endorno from (select a.endor_no from udm_policy_salary a,udm_policy_main b
                                               where a.policy_no=b.policy_no and a.policy_no=v_policy_no order by b.endor_sn desc) aa where rownum=1;
end if;
end if;
insert into UDM_P_THIRD_SALARYTEMP(
    id,main_id,second_id,policy_no ,endor_no,
    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
    ccaltype,hedge_sencondid,hedge_thirdid,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(Seq_Udm_p_Tsalaryid.Nextval, 12, '0'),v_newMainID2,tt4.id,tt3.policy_no ,tt3.endor_no,
       tt4.crct_no,tt3.operator_code,tt4.fee_rate,tt1.fee_amount,tt1.fee_amount_cny,
       tt4.ccaltype,null,null,'Z'
from UDM_P_THIRD_SALARY_Archive tt1,udm_policy_main tt3,udm_policy_salary tt4
where tt1.policy_no = tt3.policy_no
  and tt3.endor_no = v_endor_no
  and tt4.policy_no = tt3.policy_no
  and tt4.endor_no = v_salary_endorno
  and tt1.main_id = p.id;
end LOOP;

            --同时冲负且冲正数据，冲正数据的最新保费需要特殊处理
         if v_premium_amount=0 then
            for p in (select distinct a.endor_no,a.kind_sn,a.kind_code from UDM_P_THIRDPREMIUMTEMP a where a.endor_no=v_endor_no and a.reflag='Z')loop
select count(1) into v_count
from udm_policy_coverage a where a.endor_no=p.endor_no and nvl(a.kind_sn,1)=nvl(p.kind_sn,1)
                             and a.kind_code = p.kind_code;
if v_count=0 then --批单找不到该责任 则取上一个正数保费的批单的值写入，见商险三层问题32
select a.totalpremium_amount,a.totalextax_premium_amount,a.totaltax_amount,
       a.totalpremium_amount_cny,a.totalextax_premium_amount_cny,a.totaltax_amount_cny
into v_topremium_amount,v_toextax_premium_amount,v_totax_amount,
    v_topremium_amount_cny,v_toextax_premium_amount_cny,v_totax_amount_cny
from udm_policy_coverage a where nvl(a.kind_sn,1)=nvl(p.kind_sn,1)
                             and a.kind_code = p.kind_code and a.policy_no=v_policy_no and a.premium_amount>=0 order by a.timestamp desc FETCH FIRST 1 ROWS ONLY;
else
select a.totalpremium_amount,a.totalextax_premium_amount,a.totaltax_amount,
       a.totalpremium_amount_cny,a.totalextax_premium_amount_cny,a.totaltax_amount_cny
into v_topremium_amount,v_toextax_premium_amount,v_totax_amount,
    v_topremium_amount_cny,v_toextax_premium_amount_cny,v_totax_amount_cny
from udm_policy_coverage a where a.endor_no=p.endor_no and nvl(a.kind_sn,1)=nvl(p.kind_sn,1)
                             and a.kind_code = p.kind_code;
end if;
select id into v_mainid from (select a.id from UDM_P_THIRDPREMIUMTEMP a where a.endor_no=p.endor_no and  nvl(a.kind_sn,1)=nvl(p.kind_sn,1)
                                                                          and a.kind_code = p.kind_code and a.reflag='Z' order by a.id) where rownum=1;
update UDM_P_THIRDPREMIUMTEMP set totalpremium_amount=v_topremium_amount,totalextax_premium_amount=v_toextax_premium_amount,totaltax_amount=v_totax_amount,
                                  totalpremium_amount_cny=v_topremium_amount_cny,totalextax_premium_amount_cny=v_toextax_premium_amount_cny,totaltax_amount_cny=v_totax_amount_cny
where id = v_mainid;
end loop;

end if;
end if;
Exception
        when others then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdhedge',sysdate,v_endor_no,sqlcode,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'--'||substr(sqlerrm,1,1000),'udm_p_thirdhedge');
            raise;
end;


    procedure udm_p_thirdpremiumexpand(v_policy_no in varchar2,v_endor_no in varchar2,i_premium_amount in number,v_endor_type in varchar2,v_sysday in varchar2,v_sys_source in varchar2)is
        v_mainid varchar2(32);
        v_count  number(5):=0;
        v_count2  number(5):=0;
        v_f_count  number(5):=0;--对冲负数个数
        v_z_count  number(5):=0;--对冲正数个数
        v_premium_amount number(18,2):=0;
        v_extax_premium_amount number(18,2):=0;
        v_premium_amount_cny number(18,2):=0;
        v_extax_premium_amount_cny number(18,2):=0;
        v_topremium_amount number(18,2):=0;
        v_toextax_premium_amount number(18,2):=0;
        v_topremium_amount_cny number(18,2):=0;
        v_toextax_premium_amount_cny number(18,2):=0;
        v_suminsured_amount number(18,2):=0;
        v_suminsured_amount_cny number(18,2):=0;
        v_totalsuminsured_amount number(18,2):=0;--最新保额交易币
        v_totalsuminsured_amount_cny number(18,2):=0;--最新保额本位币
        v_totaldeductible_amount number(18,2):=0;--最新本位币
        v_totaldeductible_amount_cny number(18,2):=0;--最新本位币
        v_premium_amount_lastest_sum number(18,2):=0;
        v_premium_amount_cny_lastest_sum  number(18,2):=0;
        v_totalpremium_amount_lastest number(18,2):=0;
        v_totalpremium_amount_lastest_cny number(18,2):=0;

        --以下变量作为责任尾差的累加
        k_premium_amount number(18,2):=0;
        /* k_extax_premium_amount number(18,2):=0;*/
        k_premium_amount_cny number(18,2):=0;
        /*k_extax_premium_amount_cny number(18,2):=0;*/
        /* k_totalsuminsured_amount number(18,2):=0;--最新保额交易币
         k_totalsuminsured_amount_cny number(18,2):=0;--最新保额本位币
         k_totaldeductible_amount number(18,2):=0;--最新本位币
         k_totaldeductible_amount_cny number(18,2):=0;--最新本位币*/

        v_producer_type varchar2(2);
        v_producer_code varchar2(20);

        v_line_of_business varchar2(20); --三级分类代码 12CHSXHX为火险
        v_earned_startdate_enddate_hedge_way varchar(1);
        v_old_earned_startdate date;
        v_endor_sn varchar2(3);
        v_riskcode varchar2(4);
        v_lineofbusiness_lv4 varchar2(20);
        v_updatedfield varchar2(500);
begin

        --是否有对冲数据
select count(1) into v_f_count from UDM_P_THIRDPREMIUMTEMP where REFLAG='F';
select count(1) into v_z_count from UDM_P_THIRDPREMIUMTEMP where REFLAG='Z';

if v_z_count=0 and v_f_count>0 then
            --只有冲负没有冲正，则按最新值进行计算，其中保额要用历次批改加和
select max(producer_type),max(producer_code) into v_producer_type, v_producer_code
from udm_policy_comm_fee  where endor_no=v_endor_no and is_main_producer = '1';

select tt.line_of_business,tt.endor_sn,tt.risk_code,tt.updated_fields into v_line_of_business,v_endor_sn,v_riskcode,v_updatedfield from udm_policy_main tt where tt.policy_no=v_policy_no and tt.endor_no=v_endor_no;
select prod_lv4_code into v_lineofbusiness_lv4 from htudm.udm_lineofbusiness_code t where is_deleted = 0 and riskcode = v_riskcode;

--赚取起止期
if v_line_of_business = '12CHSXHX' then --火险
                if v_premium_amount != 0 and  (instr(v_updatedfield,'P005')>0 or instr(v_updatedfield,'P006')>0) then --火险--有保费变动且更改保险期间
                    v_earned_startdate_enddate_hedge_way := '1';
else --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）
                    v_earned_startdate_enddate_hedge_way := '2';
                    if v_endor_sn<>'000' then
                        v_endor_sn := TO_CHAR(TO_NUMBER(v_endor_sn) - 1, 'FM000');
end if;
select t3.earned_startdate into v_old_earned_startdate from udm_policy_main t2,UDM_POLICY_COVERAGE t3
where t2.policy_no=v_policy_no and t2.endor_sn=v_endor_sn and t3.MAIN_ID=t2.id and rownum=1;

end if;
else --非火险
                v_earned_startdate_enddate_hedge_way := '3';
end if;

for p in (select n.endor_sn,m.* from UDM_POLICY_COVERAGE m,udm_policy_main n
                      where m.endor_no=n.endor_no and m.endor_no=v_endor_no) loop
select sum(c.suminsured_amount),sum(c.suminsured_amount_cny),sum(c.deductible_amount),sum(c.deductible_amount_cny)
into v_totalsuminsured_amount,v_totalsuminsured_amount_cny,
    v_totaldeductible_amount,v_totaldeductible_amount_cny
from UDM_POLICY_COVERAGE  c,udm_policy_main d where c.endor_no=d.endor_no and d.endor_sn<=p.endor_sn
                                                and nvl(c.kind_sn,1) =nvl(p.kind_sn,1) and c.kind_code=p.kind_code and c.policy_no=p.policy_no;
--and (c.SUMINSURED_FLAG='1' or c.SUMINSURED_FLAG is null);

insert into UDM_P_THIRDPREMIUMTEMP
(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,
 endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
 risk_code ,start_date,end_date,issue_date,uw_date ,
 transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
 sale_system ,init_policy_no,inception_date,renewed_policy_no ,
 business_source ,business_type ,appli_code,
 nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,
 insuredname,is_multi_insureds ,operator_code ,
 guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,
 is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
 debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,installment_sn,
 installment_times ,payer_code,payer_name,coins_code,coins_name,
 coins_share ,coins_fee_rate,coins_type,coins_flag,
 joins_code,joins_name,joins_share ,joins_policy_no,joins_endor_no ,joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,
 treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
 k_end_date,k_valid_date,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,
 suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
 premium_amount,
 premium_currency,premium_exrate,
 premium_amount_cny,
 vat_flag,taxfree_flag,tax_rate,
 extax_premium_amount,tax_amount,extax_premium_amount_cny,
 tax_amount_cny,
    /* totalpremium_amount_share ,premium_amount_share,*/
 totalpremium_amount,totalextax_premium_amount,totaltax_amount,
 totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,REFLAG,
 PAY_START_DATE,SUBJECT_NAME,crct_no,policy_from,business_unit,warranty_period
    ,producer_type, producer_code, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,iscbc,isgiftinsurance)

select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_TPremiumid.Nextval, 12, '0') ID,a.id second_Main_ID,a.sys_source , a.proposal_no ,v_policy_no,a.declaration_no 申报号水险,
       a.endorapply_no,a.endor_no ,a.endor_sn,a.endor_type ,a.cancel_reason ,a.issue_company ,
       a.risk_code ,a.start_date,a.end_date ,a.issue_date ,a.uw_date ,
       a.transaction_date ,a.valid_date,a.renew_flag,a.statutory_accounting_period ,a.us_accounting_period ,
       a.sale_system ,a.init_policy_no ,a.inception_date ,a.renewed_policy_no ,
       a.business_source ,a.business_type ,a.appli_code ,
       f.nationalitycode,f.enterprisenature,f.htrelationship,f.circdomesticind,f.appliename,
       g.insuredname,a.is_multi_insureds ,a.operator_code,
       a.guarantee_days ,a.upc_flag ,a.domestic_flag ,a.internet_flag ,a.policy_type ,
       a.is_prepaid ,a.is_oversea ,a.is_renewable ,a.policy_renewalbe_no ,a.business_year ,
       c.debitnote_no ,a.dm_flag ,a.hisdata_flag ,a.modifydata_flag ,c.installment_sn ,
       c.installment_times ,c.payer_code ,c.payer_name ,
       decode(a.coins_type,'03',null,d.coins_code)  共保方代码,decode(a.coins_type,'03',null,d.coins_name) 共保方名称,
       d.coins_share 共保比例,d.coins_fee_rate 共保出单费比例,d.coins_type 共保类型,d.coins_flag,
       decode(a.joins_type,'03',null,e.joins_code) 联保方代码,decode(a.joins_type,'03',null,e.joins_name) 联保方名称,e.joins_share 联保比例,e.joins_policy_no 联保保单号,e.joins_endor_no,e.joins_type 联保类型,e.joins_flag,b.kind_sn 责任序号,b.kind_code 责任代码,b.kind_name 责任名称,b.kind_type 主附险标识,
       b.treaty_type,b.ht_treaty_share,b.treaty_group,b.start_date 起保日期,
       b.end_date 终保日期,b.valid_date 生效日期,
       case when c.min_sn_flag='1' then round(v_totalsuminsured_amount*d.coins_share*e.joins_share,2)
            else 0
           end as 保额变化量交易币,
       case when c.min_sn_flag='1' then round(v_totaldeductible_amount*d.coins_share*e.joins_share,2)
            else 0
           end as 免赔额变化量交易币,b.suminsured_currency 保额币种,b.suminsured_exrate 保额汇率,
       case when c.min_sn_flag='1' then round(v_totalsuminsured_amount_cny*d.coins_share*e.joins_share,2)
            else 0
           end as 保额变化量本位币,
       case when c.min_sn_flag='1' then round(v_totaldeductible_amount_cny*d.coins_share*e.joins_share,2)
            else 0
           end as 免赔额变化量本位币,b.deductible_rate 免赔率,

       case when b.premium_currency='JPY' then round(b.totalpremium_amount*c.pay_share*d.coins_share*e.joins_share,0)
            else round(b.totalpremium_amount*c.pay_share*d.coins_share*e.joins_share,2)
           end as  保费变化量交易币,
       b.premium_currency 保费币种,b.premium_exrate 保费汇率,
       round(b.totalpremium_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 保费变化量本位币,
       b.vat_flag 是否增值税,b.taxfree_flag 是否免税,b.tax_rate 税率,
       case when b.premium_currency='JPY' then round(b.totalextax_premium_amount*c.pay_share*d.coins_share*e.joins_share,0)
            else round(b.totalextax_premium_amount*c.pay_share*d.coins_share*e.joins_share,2)
           end as 不含税保费变化量交易币,
       case when b.premium_currency='JPY' then round(b.totaltax_amount*c.pay_share*d.coins_share*e.joins_share,0)
            else round(b.totaltax_amount*c.pay_share*d.coins_share*e.joins_share,2)
           end as 税额变化量交易币,
       round(b.totalextax_premium_amount_cny*c.pay_share*d.coins_share*e.joins_share,2)  不含税保费变化量本位币,
       round(b.totaltax_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 税额变化量本位币,
    /*    b.totalpremium_amount_share 当前责任最新含税保费占整单比例,b.premium_amount_share 当前责任含税保费变化量占整单变化量的比例,*/
       case when b.premium_currency='JPY' then round(b.totalpremium_amount*c.pay_share*d.coins_share*e.joins_share,0)
            else round(b.totalpremium_amount*c.pay_share*d.coins_share*e.joins_share,2)
           end as 最新保费交易币,
       case when b.premium_currency='JPY' then round(b.totalextax_premium_amount*c.pay_share*d.coins_share*e.joins_share,0)
            else round(b.totalextax_premium_amount*c.pay_share*d.coins_share*e.joins_share,2)
           end as 最新不含税保费交易币,
       case when b.premium_currency='JPY' then round(b.totaltax_amount*c.pay_share*d.coins_share*e.joins_share,0)
            else round(b.totaltax_amount*c.pay_share*d.coins_share*e.joins_share,2)
           end as 最新税额交易币,
       round(b.totalpremium_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 最新保费本位币,
       round(b.totalextax_premium_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 最新不含税保费本位币,
       round(b.totaltax_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 最新税额本位币,null,null,'N',
       c.pay_start_date,b.subject_name,c.main_id||c.installment_times||c.installment_sn||b.taxfree_flag,a.policy_from,a.business_unit,a.warranty_period
        ,v_producer_type, v_producer_code,
       case
           when v_earned_startdate_enddate_hedge_way = '1' then b.earned_startdate --火险--有保费变动且更改保险期间	新单赚取起期
           when v_earned_startdate_enddate_hedge_way = '2' then v_old_earned_startdate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）	旧单赚取起期
           else b.earned_startdate --其余场景（非火险以外的其余对冲场景）	旧单赚取起期
           end,
       case
           when v_earned_startdate_enddate_hedge_way = '1' then b.earned_enddate --火险--有保费变动且更改保险期间 新单赚取止期
           when v_earned_startdate_enddate_hedge_way = '2' then b.earned_enddate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑） 旧单赚取止期
           else b.earned_enddate --其余场景（非火险以外的其余对冲场景） 旧单赚取止期
           end,
       v_line_of_business,v_lineofbusiness_lv4,
       a.iscbc,a.isgiftinsurance
from UDM_POLICY_MAIN a,UDM_POLICY_COVERAGE b,UDM_POLICY_PAY_PLAN c,UDM_POLICY_COINS d,UDM_POLICY_JOINS e,udm_policy_holder f
   ,(select policy_no,ENDOR_NO,insuredname from udm_policy_insured where
    policy_no=v_policy_no and endor_no = v_endor_no and rownum=1) g
where a.id = b.main_id
  and a.id = c.main_id
  and a.id = d.main_id
  and a.id = e.main_id
  and a.id = f.main_id
  and b.id = p.id
  and a.endor_no = g.endor_no
  and a.endor_no = p.endor_no
--and (b.sumpreum_flag='Y' or b.sumpreum_flag is null)
;
end loop;

        elsif v_endor_type in ('1010' ,'1040') or i_premium_amount!=0 or (v_f_count=0 and v_z_count=0)then
            --费用批改、有保费变化的
select max(producer_type),max(producer_code) into v_producer_type, v_producer_code
from udm_policy_comm_fee  where endor_no=v_endor_no and is_main_producer = '1';

select tt.line_of_business,tt.endor_sn,tt.risk_code,tt.updated_fields into v_line_of_business,v_endor_sn,v_riskcode,v_updatedfield from udm_policy_main tt where tt.policy_no=v_policy_no and tt.endor_no=v_endor_no;
select prod_lv4_code into v_lineofbusiness_lv4 from htudm.udm_lineofbusiness_code t where is_deleted = 0 and riskcode = v_riskcode;

--赚取起止期
if v_line_of_business = '12CHSXHX' then --火险
                if v_premium_amount != 0 and (instr(v_updatedfield,'P005')>0 or instr(v_updatedfield,'P006')>0) then --火险--有保费变动且更改保险期间
                    v_earned_startdate_enddate_hedge_way := '1';
else --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）
                    v_earned_startdate_enddate_hedge_way := '2';
                    if v_endor_sn<>'000' then
                        v_endor_sn := TO_CHAR(TO_NUMBER(v_endor_sn) - 1, 'FM000');
end if;
select t3.earned_startdate into v_old_earned_startdate from udm_policy_main t2,UDM_POLICY_COVERAGE t3
where t2.policy_no=v_policy_no and t2.endor_sn=v_endor_sn and t3.MAIN_ID=t2.id and rownum=1;

end if;
else --非火险
                v_earned_startdate_enddate_hedge_way := '3';
end if;

insert into UDM_P_THIRDPREMIUMTEMP
(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,
 endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
 risk_code ,start_date,end_date,issue_date,uw_date ,
 transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
 sale_system ,init_policy_no,inception_date,renewed_policy_no ,
 business_source ,business_type ,appli_code,
 nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,
 insuredname,is_multi_insureds ,operator_code ,
 guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,
 is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
 debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,installment_sn,
 installment_times ,payer_code,payer_name,coins_code,coins_name,
 coins_share ,coins_fee_rate,coins_type,coins_flag,
 joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,
 treaty_type ,ht_treaty_share ,treaty_group,/*suminsured_flag ,*/k_start_date,
 k_end_date,k_valid_date,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,
 suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
 premium_amount,
 premium_currency,premium_exrate,
 premium_amount_cny,
 vat_flag,taxfree_flag,tax_rate,
 extax_premium_amount,tax_amount,extax_premium_amount_cny,
 tax_amount_cny,
    /* totalpremium_amount_share ,premium_amount_share,*/
 totalpremium_amount,totalextax_premium_amount,totaltax_amount,
 totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,REFLAG,
 PAY_START_DATE,SUBJECT_NAME,crct_no,policy_from,business_unit,warranty_period
    ,producer_type, producer_code, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,iscbc,isgiftinsurance)

select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_TPremiumid.Nextval, 12, '0') ID,a.id second_Main_ID,a.sys_source 系统来源, a.proposal_no 投保单号,v_policy_no,a.declaration_no 申报号水险,
       a.endorapply_no,a.endor_no 批单号,a.endor_sn 批改序号,a.endor_type 批改类型,a.cancel_reason 取消原因代码,a.issue_company 归属机构,
       a.risk_code 险种代码,a.start_date 保批单起保日期,a.end_date 保批单终保日期,a.issue_date 保批单签单日期,a.uw_date 保批单核保核批日期,
       a.transaction_date 保批单交易日期,a.valid_date 保批单生效日期,a.renew_flag 续保标识,a.statutory_accounting_period 财务挂账日期自然月账期,a.us_accounting_period 财务挂账日期USGAP,
       a.sale_system 销售体系,a.init_policy_no Frist保单号,a.inception_date 第一张保单的生效日,a.renewed_policy_no 续保单续的上一张保单号,
       a.business_source 业务来源,a.business_type 业务类型,a.appli_code ,
       f.nationalitycode,f.enterprisenature,f.htrelationship,f.circdomesticind,f.appliename,
       g.insuredname,a.is_multi_insureds 多被保险人标识,a.operator_code 业务员代码,
       a.guarantee_days 保证期天数,a.upc_flag 延保业务UPC标识,a.domestic_flag MN业务类型,a.internet_flag 互联网标识,a.policy_type 保单类型,
       a.is_prepaid 预付保费标识,a.is_oversea 海外业务标识,a.is_renewable 可续不可续标识,a.policy_renewalbe_no 水险协议保单的协议号,a.business_year 业务年度,
       c.debitnote_no 安达账单号,a.dm_flag 迁移标记,a.hisdata_flag 历史数据标志,a.modifydata_flag 是否修数标志,c.installment_sn 期别序号,
       c.installment_times 缴费总期数,c.payer_code 付款人编码,c.payer_name 付款人名称,
       decode(a.coins_type,'03',null,d.coins_code)  共保方代码,decode(a.coins_type,'03',null,d.coins_name) 共保方名称,
       d.coins_share 共保比例,d.coins_fee_rate 共保出单费比例,d.coins_type 共保类型,d.coins_flag,
       decode(a.joins_type,'03',null,e.joins_code) 联保方代码,decode(a.joins_type,'03',null,e.joins_name) 联保方名称,e.joins_share 联保比例,e.joins_policy_no 联保保单号,
       e.joins_endor_no,e.joins_type 联保类型,e.joins_flag,b.kind_sn 责任序号,b.kind_code 责任代码,b.kind_name 责任名称,b.kind_type 主附险标识,
       b.treaty_type 再保合约属性,b.ht_treaty_share 再保华泰合约占比,b.treaty_group 合约组代码,b.start_date 起保日期,
       b.end_date 终保日期,b.valid_date 生效日期,
       case when c.min_sn_flag='1' then round(b.suminsured_amount*d.coins_share*e.joins_share,2)
            else 0
           end as 保额变化量交易币,
       case when c.min_sn_flag='1' then round(b.deductible_amount*d.coins_share*e.joins_share,2)
            else 0
           end as 免赔额变化量交易币,b.suminsured_currency 保额币种,b.suminsured_exrate 保额汇率,
       case when c.min_sn_flag='1' then round(b.suminsured_amount_cny*d.coins_share*e.joins_share,2)
            else 0
           end as 保额变化量本位币,
       case when c.min_sn_flag='1' then round(b.deductible_amount_cny*d.coins_share*e.joins_share,2)
            else 0
           end as 免赔额变化量本位币,b.deductible_rate 免赔率,
       round(b.premium_amount*c.pay_share*d.coins_share*e.joins_share,2) 保费变化量交易币,
       b.premium_currency 保费币种,b.premium_exrate 保费汇率,
       round(b.premium_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 保费变化量本位币,
       b.vat_flag 是否增值税,b.taxfree_flag 是否免税,b.tax_rate 税率,
       round(b.extax_premium_amount*c.pay_share*d.coins_share*e.joins_share,2) 不含税保费变化量交易币,
       round(b.tax_amount*c.pay_share*d.coins_share*e.joins_share,2) 税额变化量交易币,
       round(b.extax_premium_amount_cny*c.pay_share*d.coins_share*e.joins_share,2)  不含税保费变化量本位币,
       round(b.tax_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 税额变化量本位币,
    /*    b.totalpremium_amount_share 当前责任最新含税保费占整单比例,b.premium_amount_share 当前责任含税保费变化量占整单变化量的比例,*/
       round(b.totalpremium_amount*c.pay_share*d.coins_share*e.joins_share,2) 最新保费交易币,
       round(b.totalextax_premium_amount*c.pay_share*d.coins_share*e.joins_share,2) 最新不含税保费交易币,
       round(b.totaltax_amount*c.pay_share*d.coins_share*e.joins_share,2) 最新税额交易币,
       round(b.totalpremium_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 最新保费本位币,
       round(b.totalextax_premium_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 最新不含税保费本位币,
       round(b.totaltax_amount_cny*c.pay_share*d.coins_share*e.joins_share,2) 最新税额本位币,null,null,'N',
       c.pay_start_date,b.subject_name,c.main_id||c.installment_times||c.installment_sn||b.taxfree_flag,a.policy_from,a.business_unit,a.warranty_period
        ,v_producer_type, v_producer_code,
       case
           when v_earned_startdate_enddate_hedge_way = '1' then b.earned_startdate --火险--有保费变动且更改保险期间	新单赚取起期
           when v_earned_startdate_enddate_hedge_way = '2' then v_old_earned_startdate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑）	旧单赚取起期
           else b.earned_startdate --其余场景（非火险以外的其余对冲场景）	旧单赚取起期
           end,
       case
           when v_earned_startdate_enddate_hedge_way = '1' then b.earned_enddate --火险--有保费变动且更改保险期间 新单赚取止期
           when v_earned_startdate_enddate_hedge_way = '2' then b.earned_enddate --火险-其余场景（含无保费变动、有保费变动变更机构等，此为火险兜底逻辑） 旧单赚取止期
           else b.earned_enddate --其余场景（非火险以外的其余对冲场景） 旧单赚取止期
           end,
       v_line_of_business,v_lineofbusiness_lv4,a.iscbc,a.isgiftinsurance
from UDM_POLICY_MAIN a,UDM_POLICY_COVERAGE b,UDM_POLICY_PAY_PLAN c,UDM_POLICY_COINS d,UDM_POLICY_JOINS e,UDM_POLICY_HOLDER f
   ,(select policy_no,ENDOR_NO,insuredname from udm_policy_insured where
    policy_no=v_policy_no and endor_no = v_endor_no and rownum=1) g
where a.id = b.main_id
  and a.id = c.main_id
  and a.id = d.main_id
  and a.id = e.main_id
  and a.id = f.main_id
  and a.policy_no = g.policy_no and a.endor_no = g.endor_no
  and a.policy_no=v_policy_no and a.endor_no = v_endor_no;
end if;

         --仅需要对非对冲数据进行尾差、税额、价税拆分处理
select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP where REFLAG='N';
select count(1) into v_count2 from UDM_P_THIRDPREMIUMTEMP where REFLAG='F' or REFLAG='Z';

if v_endor_no='CS036249712400000001E01' then
          -- 尾差处理
          if v_count > 0 and v_count2 = 0 then --普通展开、无对冲数据
              --把三层数据按期别group by后sum，sum与二层该期的差额，轧差该期保费最高的那条上面
              --期别 保费最高的上面，每一期分别对上，和总的保费对上，不一定全部完美匹配。
              --期别、金额排序（保费最高的上面），负的都行
              for p in (select a.endor_no,a.installment_sn,sum(a.premium_amount) premium_amount,sum(a.premium_amount_cny) premium_amount_cny
                        from UDM_P_THIRDPREMIUMTEMP a where a.endor_no=v_endor_no and REFLAG='N' group by a.endor_no,a.installment_sn) loop

                      -- 含税保费交易币、含税保费本位币尾差处理
select p.premium_amount - a.premium_amount,
       p.premium_amount_cny - a.premium_amount_cny
into   v_premium_amount,
    v_premium_amount_cny
from udm_policy_pay_plan a where a.endor_no=p.endor_no and a.installment_sn=p.installment_sn;
--DBMS_OUTPUT.put_line(p.installment_sn||'=='||v_premium_amount||'=='||v_premium_amount_cny);
if v_premium_amount != 0  or v_premium_amount_cny != 0
                         then
select cc.id into v_mainid from UDM_P_THIRDPREMIUMTEMP cc
where cc.REFLAG='N' and cc.installment_sn = p.installment_sn and cc.endor_no=v_endor_no order by abs(premium_amount) desc fetch first 1 row only;
end if;
update UDM_P_THIRDPREMIUMTEMP tt
set tt.premium_amount                = tt.premium_amount - v_premium_amount,
    tt.extax_premium_amount          = tt.extax_premium_amount - v_premium_amount,
    tt.premium_amount_cny            = tt.premium_amount_cny - v_premium_amount_cny,
    tt.extax_premium_amount_cny      = tt.extax_premium_amount_cny - v_premium_amount_cny,
    tt.tax_amount                    = (tt.premium_amount - v_premium_amount) - (tt.extax_premium_amount - v_premium_amount),
    tt.tax_amount_cny                = (tt.premium_amount_cny - v_premium_amount_cny) - (tt.extax_premium_amount_cny - v_premium_amount_cny),
    tt.tailDiff_flag = 'Y'
where tt.id = v_mainid;
end loop;
          elsif v_count > 0 and v_count2 > 0 then --普通展开、有对冲数据
              --轧差方法：批单重发的 = 批单最新保费
              -- 批单重发的交易币、本位币保费，即批单最新交易币、本位币保费
select sum(a.premium_amount) premium_amount, sum(a.premium_amount_cny) premium_amount_cny
into v_premium_amount_lastest_sum, v_premium_amount_cny_lastest_sum from UDM_P_THIRDPREMIUMTEMP a where a.endor_no=v_endor_no and REFLAG='N';
select totalpremium_amount,totalpremium_amount_cny into v_totalpremium_amount_lastest,v_totalpremium_amount_lastest_cny from udm_policy_main t where endor_no=v_endor_no;
v_premium_amount := v_premium_amount_lastest_sum - v_totalpremium_amount_lastest;
              v_premium_amount_cny := v_premium_amount_cny_lastest_sum - v_totalpremium_amount_lastest_cny;

              DBMS_OUTPUT.put_line('taildiff a'||v_premium_amount||'=='||v_premium_amount_cny);
              if v_premium_amount != 0 or v_premium_amount_cny != 0 then
select cc.id into v_mainid from UDM_P_THIRDPREMIUMTEMP cc
where cc.REFLAG='N' and cc.endor_no=v_endor_no order by abs(premium_amount) desc fetch first 1 row only;

update UDM_P_THIRDPREMIUMTEMP tt
set tt.premium_amount                = tt.premium_amount - v_premium_amount,
    tt.extax_premium_amount          = tt.extax_premium_amount - v_premium_amount,
    tt.premium_amount_cny            = tt.premium_amount_cny - v_premium_amount_cny,
    tt.extax_premium_amount_cny      = tt.extax_premium_amount_cny - v_premium_amount_cny,
    tt.tax_amount                    = (tt.premium_amount - v_premium_amount) - (tt.extax_premium_amount - v_premium_amount),
    tt.tax_amount_cny                = (tt.premium_amount_cny - v_premium_amount_cny) - (tt.extax_premium_amount_cny - v_premium_amount_cny),
    tt.tailDiff_flag = 'Y'
where tt.id = v_mainid;
end if;
end if;
end if ;

        --仅需要对非对冲数据进行尾差、税额、价税拆分处理
select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP where REFLAG='N';
select count(1) into v_count2 from UDM_P_THIRDPREMIUMTEMP where REFLAG='F' or REFLAG='Z';

--
-- DBMS_OUTPUT.put_line('主表insert完成='||v_count);
--for p in (select * from UDM_P_THIRDPREMIUMTEMP a where REFLAG='N' order by a.installment_sn,a.coins_code,a.kind_code)loop
--  DBMS_OUTPUT.put_line(p.installment_sn||'='||p.coins_code||'='||p.kind_code||'='||p.premium_amount||'=='||p.extax_premium_amount);
--end loop;

if v_count > 0 and v_count2 = 0 and 1=2 then
            --冲负后，按最新值计算得出的金额全为0，即保费变化后的总保费金额为0的情况，此时缴费计划表只有1期，无需根据缴费计划处理尾差
select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP where endor_no=v_endor_no and REFLAG='N' and premium_amount!=0;
if v_z_count=0 and v_f_count>0 and v_count=0 then
                --do nothing
select '1' into v_mainid from dual;
else
                --最终以缴费计划表的为准处理尾差，需保证按缴费计划、联共保、责任保费2、3层都能对上
                for p in (select a.endor_no,a.installment_sn,sum(a.premium_amount) premium_amount,sum(a.premium_amount_cny) premium_amount_cny
                          from UDM_P_THIRDPREMIUMTEMP a where a.endor_no=v_endor_no and REFLAG='N' group by a.endor_no,a.installment_sn) loop
select (p.premium_amount-a.premium_amount),(p.premium_amount_cny-a.premium_amount_cny)
into v_premium_amount,v_premium_amount_cny
from udm_policy_pay_plan a where a.endor_no=p.endor_no and a.installment_sn=p.installment_sn;
--DBMS_OUTPUT.put_line(p.installment_sn||'=='||v_premium_amount||'=='||v_premium_amount_cny);
if v_premium_amount!=0 or v_premium_amount_cny!=0 then
                            --优先在不平的联共保业务上进行尾差处理，其次在不平的责任上进行尾差处理，如果都平，则挑选当期某一条处理差额
                            --缴费计划的尾差>责任的尾差时，要循环责任处理，不能加到某一条责任上

                            v_count := 0;
                            k_premium_amount := 0;
                            k_premium_amount_cny := 0;

                            /*for t in (select tmain.endor_no,nvl(tmain.kind_sn,1) kind_sn,tmain.kind_code,sum(tmain.premium_amount) premium_amount,
                               sum(tmain.premium_amount_cny) premium_amount_cny
                                from UDM_P_THIRDPREMIUMTEMP tmain where (tmain.premium_amount!=0 or tmain.premium_amount_cny!=0)  and REFLAG='N'
                                 and tmain.endor_no=p.endor_no group by tmain.endor_no,nvl(tmain.kind_sn,1),tmain.kind_code) loop
                              DBMS_OUTPUT.put_line('A='||t.kind_sn||'='||t.premium_amount||'='||t.premium_amount_cny);
                            end loop;

                            for t in (select * from udm_policy_coverage u where u.endor_no=v_endor_no and u.premium_amount!=0 order by u.kind_sn) loop
                              DBMS_OUTPUT.put_line('B='||t.kind_sn||'='||t.premium_amount||'='||t.premium_amount_cny);
                            end loop;*/

                            -- 对比UDM_P_THIRDPREMIUMTEMP按批单号、责任序号、责任编码汇总的保费（交易币、本位币）与责任表对应保费不等
for kind in (select aa.endor_no,aa.kind_sn,aa.kind_code,aa.premium_amount-bb.premium_amount i_premium_amount,aa.premium_amount_cny-bb.premium_amount_cny i_premium_amount_cny
                                         from (select tmain.endor_no,nvl(tmain.kind_sn,1) kind_sn,tmain.kind_code,sum(tmain.premium_amount) premium_amount,
                                                      sum(tmain.premium_amount_cny) premium_amount_cny
                                               from UDM_P_THIRDPREMIUMTEMP tmain where (tmain.premium_amount!=0 or tmain.premium_amount_cny!=0)  and REFLAG='N'
                                                                                   and tmain.endor_no=p.endor_no group by tmain.endor_no,nvl(tmain.kind_sn,1),tmain.kind_code) aa,udm_policy_coverage bb
                                         where aa.kind_code = bb.kind_code and aa.kind_sn = nvl(bb.kind_sn,1) and aa.endor_no = bb.endor_no
                                           and aa.endor_no = v_endor_no
                                           and (aa.premium_amount!=bb.premium_amount or aa.premium_amount_cny!=bb.premium_amount_cny) order by aa.kind_sn,aa.kind_code) loop

select cc.id into v_mainid from UDM_P_THIRDPREMIUMTEMP cc
where cc.REFLAG='N' and cc.kind_code=kind.kind_code and nvl(cc.kind_sn,1)=kind.kind_sn
  and cc.installment_sn = p.installment_sn and rownum=1; --rownum=1二层联共保表缺少本位币字段，无法核对。本应该考虑联共保方的维度，目前暂不考虑，所以这里当某个责任、期别，存在2个联共保方无法确认放在哪个联共保方时，先随机取一个联共保方(rownum=1),将尾差加到其上(这条三层表记录上)。

k_premium_amount := k_premium_amount + kind.i_premium_amount;
                                    k_premium_amount_cny := k_premium_amount_cny + kind.i_premium_amount_cny;
                                    v_count := v_count+1;
                                    --DBMS_OUTPUT.put_line(p.installment_sn||'='||kind.kind_sn||'='||kind.kind_code||'='||kind.i_premium_amount||'='||kind.i_premium_amount_cny||'='||v_premium_amount||'='||v_premium_amount_cny||'='||v_count||'--'||k_premium_amount);
                                    if (v_premium_amount>0 or v_premium_amount_cny>0) and (k_premium_amount <= v_premium_amount or k_premium_amount_cny <= v_premium_amount_cny) then
                                        --DBMS_OUTPUT.put_line('==M=='||v_count);
update UDM_P_THIRDPREMIUMTEMP tt set tt.premium_amount = tt.premium_amount-kind.i_premium_amount,tt.extax_premium_amount = tt.extax_premium_amount-kind.i_premium_amount,
                                     tt.premium_amount_cny = tt.premium_amount_cny-kind.i_premium_amount_cny,tt.extax_premium_amount_cny = tt.extax_premium_amount_cny-kind.i_premium_amount_cny,
                                     tt.totalpremium_amount=tt.totalpremium_amount-kind.i_premium_amount,
                                     tt.totalextax_premium_amount = tt.totalextax_premium_amount-kind.i_premium_amount,
                                     tt.totalpremium_amount_cny=tt.totalpremium_amount_cny-kind.i_premium_amount_cny,
                                     tt.totalextax_premium_amount_cny = tt.totalextax_premium_amount_cny-kind.i_premium_amount_cny,tt.tailDiff_flag='Y'
where tt.id = v_mainid;
elsif (v_premium_amount<0 or v_premium_amount_cny<0) and (k_premium_amount <= abs(v_premium_amount) or k_premium_amount_cny <= abs(v_premium_amount_cny)) then
                                        --DBMS_OUTPUT.put_line('==N=='||v_count);
update UDM_P_THIRDPREMIUMTEMP tt set tt.premium_amount = tt.premium_amount-v_premium_amount,tt.extax_premium_amount = tt.extax_premium_amount-v_premium_amount,
                                     tt.premium_amount_cny = tt.premium_amount_cny-v_premium_amount_cny,tt.extax_premium_amount_cny = tt.extax_premium_amount_cny-v_premium_amount_cny,
                                     tt.totalpremium_amount=tt.totalpremium_amount-v_premium_amount,
                                     tt.totalextax_premium_amount = tt.totalextax_premium_amount-v_premium_amount,
                                     tt.totalpremium_amount_cny=tt.totalpremium_amount_cny-v_premium_amount_cny,
                                     tt.totalextax_premium_amount_cny = tt.totalextax_premium_amount_cny-v_premium_amount_cny,tt.tailDiff_flag='Y'
where tt.id = v_mainid;
end if;

end loop;
                            --如果缴费计划不等，没有不等的责任，则尾差加到某一条上
                            if v_count=0 then
select mid into v_mainid
from (select tmain.id mid
      from UDM_P_THIRDPREMIUMTEMP tmain where tmain.premium_amount!=0  and REFLAG='N' and tmain.installment_sn=p.installment_sn
                                                                          and tmain.endor_no=p.endor_no order by tmain.endor_no,nvl(tmain.kind_sn,1),tmain.kind_code,tmain.coins_code,tmain.joins_code) aa where rownum=1;

update UDM_P_THIRDPREMIUMTEMP tt set tt.premium_amount = tt.premium_amount-v_premium_amount,tt.extax_premium_amount = tt.extax_premium_amount-v_premium_amount,
                                     tt.premium_amount_cny = tt.premium_amount_cny-v_premium_amount_cny,tt.extax_premium_amount_cny = tt.extax_premium_amount_cny-v_premium_amount_cny,
                                     tt.totalpremium_amount=tt.totalpremium_amount-v_premium_amount,
                                     tt.totalextax_premium_amount = tt.totalextax_premium_amount-v_premium_amount,
                                     tt.totalpremium_amount_cny=tt.totalpremium_amount_cny-v_premium_amount_cny,
                                     tt.totalextax_premium_amount_cny = tt.totalextax_premium_amount_cny-v_premium_amount_cny,tt.tailDiff_flag='Y'
where tt.id = v_mainid;
end if;

                            /*select count(1) into v_count
                             from (select tmain.endor_no,nvl(tmain.kind_sn,1) kind_sn,tmain.kind_code,sum(tmain.premium_amount) premium_amount,
                             sum(tmain.premium_amount_cny) premium_amount_cny
                              from UDM_P_THIRDPREMIUMTEMP tmain where (tmain.premium_amount!=0 or tmain.premium_amount_cny!=0)  and REFLAG='N'
                               and tmain.endor_no=p.endor_no group by tmain.endor_no,nvl(tmain.kind_sn,1),tmain.kind_code) aa,udm_policy_coverage bb
                             where aa.kind_code = bb.kind_code
                               and aa.kind_sn = nvl(bb.kind_sn,1)
                               and aa.endor_no = bb.endor_no
                               and (aa.premium_amount!=bb.premium_amount or aa.premium_amount_cny!=bb.premium_amount_cny);
                            if v_count>0 then
                              select mid into v_mainid
                              from (select cc.id mid
                               from (select tmain.endor_no,nvl(tmain.kind_sn,1) kind_sn, tmain.kind_code,sum(tmain.premium_amount) premium_amount,
                               sum(tmain.premium_amount_cny) premium_amount_cny
                               from UDM_P_THIRDPREMIUMTEMP tmain where (tmain.premium_amount!=0 or tmain.premium_amount_cny!=0)  and REFLAG='N'
                               and tmain.endor_no=p.endor_no group by tmain.endor_no,nvl(tmain.kind_sn,1),tmain.kind_code) aa,udm_policy_coverage bb,UDM_P_THIRDPREMIUMTEMP cc
                               where aa.kind_code = bb.kind_code
                               and aa.kind_sn = nvl(bb.kind_sn,1)
                               and aa.endor_no = bb.endor_no
                               and (aa.premium_amount!=bb.premium_amount or aa.premium_amount_cny!=bb.premium_amount_cny)
                               and aa.endor_no = cc.endor_no and (cc.premium_amount!=0 or cc.premium_amount_cny!=0)  and REFLAG='N'
                               and aa.kind_code=cc.kind_code and aa.kind_sn=nvl(cc.kind_sn,1)
                               and cc.installment_sn = p.installment_sn
                               order by cc.kind_sn,cc.kind_code
                               ) where rownum=1;
                            else
                              select mid into v_mainid
                              from (select tmain.id mid
                               from UDM_P_THIRDPREMIUMTEMP tmain where tmain.premium_amount!=0  and REFLAG='N' and tmain.installment_sn=p.installment_sn
                               and tmain.endor_no=p.endor_no order by tmain.endor_no,nvl(tmain.kind_sn,1),tmain.kind_code,tmain.coins_code,tmain.joins_code) aa where rownum=1;

                              update UDM_P_THIRDPREMIUMTEMP tt set tt.premium_amount = tt.premium_amount-v_premium_amount,tt.extax_premium_amount = tt.extax_premium_amount-v_premium_amount,
                              tt.premium_amount_cny = tt.premium_amount_cny-v_premium_amount_cny,tt.extax_premium_amount_cny = tt.extax_premium_amount_cny-v_premium_amount_cny,
                              tt.totalpremium_amount=tt.totalpremium_amount-v_premium_amount,
                              tt.totalextax_premium_amount = tt.totalextax_premium_amount-v_premium_amount,
                              tt.totalpremium_amount_cny=tt.totalpremium_amount_cny-v_premium_amount_cny,
                              tt.totalextax_premium_amount_cny = tt.totalextax_premium_amount_cny-v_premium_amount_cny,tt.tailDiff_flag='Y'
                              where tt.id = v_mainid;

                            end if;*/

end if;
end loop;
end if;

            --for p in (select * from UDM_P_THIRDPREMIUMTEMP a where REFLAG='N' order by a.installment_sn,a.coins_code,a.kind_code)loop
            --  DBMS_OUTPUT.put_line(p.installment_sn||'='||p.coins_code||'='||p.kind_code||'=premium_amount='||p.premium_amount||'=premium_amount_cny='||p.premium_amount_cny||'=extax_premium_amount='||p.extax_premium_amount||'=extax_premium_amount_cny='||p.extax_premium_amount_cny||'=totalpremium_amount='||p.totalpremium_amount||'=totalpremium_amount_cny='||p.totalpremium_amount_cny);
            --end loop;

            --2.1、处理尾差--保费--先按责任表的处理尾差，最终以缴费计划表的总金额为准(剔除对冲数据)
            if v_z_count=0 and v_f_count>0 then
                --只冲负未冲正，冲正重新算时，尾差也需要使用最新保费比较
                --因保费变化量为后端使用的最终值，故保证premium_amount、extax_premium_amount和tax_amount是对的，不能剔除对冲数据
                for p in (select a.endor_no,a.kind_sn,a.kind_code,sum(a.premium_amount) premium_amount,sum(a.extax_premium_amount) extax_premium_amount,
                                 sum(a.premium_amount_cny) premium_amount_cny,sum(a.extax_premium_amount_cny) extax_premium_amount_cny,
                                 sum(a.totalpremium_amount) totalpremium_amount,sum(a.totalextax_premium_amount) totalextax_premium_amount,
                                 sum(a.totalpremium_amount_cny) totalpremium_amount_cny,sum(a.totalextax_premium_amount_cny) totalextax_premium_amount_cny
                          from UDM_P_THIRDPREMIUMTEMP a where a.endor_no=v_endor_no group by a.endor_no,a.kind_sn,a.kind_code) loop
                        --DBMS_OUTPUT.put_line('1='||p.kind_sn||'=='||p.kind_code);
select count(1) into v_count from udm_policy_coverage a where a.endor_no=p.endor_no and nvl(a.kind_sn,1)=nvl(p.kind_sn,1)
                                                          and a.kind_code = p.kind_code;
if v_count>0 then
select (p.premium_amount-a.premium_amount),(p.extax_premium_amount-a.extax_premium_amount),
       (p.premium_amount_cny-a.premium_amount_cny),(p.extax_premium_amount_cny-a.extax_premium_amount_cny),
       (p.totalpremium_amount-a.totalpremium_amount),(p.totalextax_premium_amount-a.totalextax_premium_amount),
       (p.totalpremium_amount_cny-a.totalpremium_amount_cny),(p.totalextax_premium_amount_cny-a.totalextax_premium_amount_cny)
into v_premium_amount,v_extax_premium_amount,v_premium_amount_cny,v_extax_premium_amount_cny,
    v_topremium_amount,v_toextax_premium_amount,v_topremium_amount_cny,v_toextax_premium_amount_cny
from udm_policy_coverage a where a.endor_no=p.endor_no and nvl(a.kind_sn,1)=nvl(p.kind_sn,1)
                             and a.kind_code = p.kind_code;
end if;

                        if v_premium_amount!=0 or v_extax_premium_amount!=0 or v_premium_amount_cny!=0 or v_extax_premium_amount_cny!=0 or
                           v_topremium_amount!=0 or v_toextax_premium_amount!=0 or v_topremium_amount_cny!=0 or v_toextax_premium_amount_cny!=0
                        then
select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP tmain where tmain.premium_amount!=0  and REFLAG='N'
                                                                                             and nvl(tmain.kind_sn,1)=nvl(p.kind_sn,1) and tmain.kind_code=p.kind_code and tmain.endor_no=v_endor_no;
if v_count>0 then
select mid into v_mainid
from (select tmain.id mid from UDM_P_THIRDPREMIUMTEMP tmain
      where (tmain.premium_amount!=0 or tmain.totalpremium_amount!=0 or tmain.extax_premium_amount!=0 or tmain.totalextax_premium_amount!=0)  and REFLAG='N'
        and nvl(tmain.kind_sn,1)=nvl(p.kind_sn,1) and tmain.kind_code=p.kind_code and tmain.endor_no=p.endor_no
      order by tmain.taildiff_flag,nvl(tmain.kind_sn,1),tmain.kind_code,tmain.installment_sn,tmain.coins_code,tmain.joins_code)
where rownum=1;
--DBMS_OUTPUT.put_line('责任金额===='||v_mainid);
update UDM_P_THIRDPREMIUMTEMP tt set tt.premium_amount = tt.premium_amount-v_premium_amount,tt.extax_premium_amount = tt.extax_premium_amount-v_extax_premium_amount,
                                     tt.premium_amount_cny = tt.premium_amount_cny-v_premium_amount_cny,tt.extax_premium_amount_cny = tt.extax_premium_amount_cny-v_extax_premium_amount_cny,
                                     tt.totalpremium_amount=tt.totalpremium_amount-v_topremium_amount,
                                     tt.totalextax_premium_amount = tt.totalextax_premium_amount-v_toextax_premium_amount,
                                     tt.totalpremium_amount_cny=tt.totalpremium_amount_cny-v_topremium_amount_cny,
                                     tt.totalextax_premium_amount_cny = tt.totalextax_premium_amount_cny-v_toextax_premium_amount_cny,tt.tailDiff_flag='Y'
where tt.id = v_mainid;
end if;
end if;
end loop;
else
                for p in (select a.endor_no,a.kind_sn,a.kind_code,sum(a.premium_amount) premium_amount,sum(a.extax_premium_amount) extax_premium_amount,
                                 sum(a.premium_amount_cny) premium_amount_cny,sum(a.extax_premium_amount_cny) extax_premium_amount_cny,
                                 sum(a.totalpremium_amount) totalpremium_amount,sum(a.totalextax_premium_amount) totalextax_premium_amount,
                                 sum(a.totalpremium_amount_cny) totalpremium_amount_cny,sum(a.totalextax_premium_amount_cny) totalextax_premium_amount_cny
                          from UDM_P_THIRDPREMIUMTEMP a where REFLAG='N' and a.endor_no=v_endor_no group by a.endor_no,a.kind_sn,a.kind_code) loop
select (p.premium_amount-a.premium_amount),(p.extax_premium_amount-a.extax_premium_amount),
       (p.premium_amount_cny-a.premium_amount_cny),(p.extax_premium_amount_cny-a.extax_premium_amount_cny),
       (p.totalpremium_amount-a.totalpremium_amount),(p.totalextax_premium_amount-a.totalextax_premium_amount),
       (p.totalpremium_amount_cny-a.totalpremium_amount_cny),(p.totalextax_premium_amount_cny-a.totalextax_premium_amount_cny)
into v_premium_amount,v_extax_premium_amount,v_premium_amount_cny,v_extax_premium_amount_cny,
    v_topremium_amount,v_toextax_premium_amount,v_topremium_amount_cny,v_toextax_premium_amount_cny
from udm_policy_coverage a where a.endor_no=p.endor_no and nvl(a.kind_sn,1)=nvl(p.kind_sn,1)
                             and a.kind_code = p.kind_code;
/*if v_premium_amount!=0 or v_extax_premium_amount!=0 or v_premium_amount_cny!=0 or v_extax_premium_amount_cny!=0 then
  --DBMS_OUTPUT.put_line(p.kind_sn||'=='||p.kind_code||'=v_premium_amount='||v_premium_amount||'=v_extax_premium_amount='||v_extax_premium_amount||'=v_premium_amount_cny='|| v_premium_amount_cny||'=v_extax_premium_amount_cny='||v_extax_premium_amount_cny ||'=v_topremium_amount='||v_topremium_amount ||'=v_toextax_premium_amount='||v_toextax_premium_amount||'=v_toextax_premium_amount_cny='||v_toextax_premium_amount_cny);
  DBMS_OUTPUT.put_line(p.kind_sn||'=='||p.kind_code||'=v_premium_amount='||v_premium_amount||'=v_extax_premium_amount='||v_extax_premium_amount);
end if;*/

if v_premium_amount!=0 or v_extax_premium_amount!=0 or v_premium_amount_cny!=0 or v_extax_premium_amount_cny!=0 or
                           v_topremium_amount!=0 or v_toextax_premium_amount!=0 or v_topremium_amount_cny!=0 or v_toextax_premium_amount_cny!=0
                        then
select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP tmain where tmain.premium_amount!=0  and REFLAG='N'
                                                                                             and nvl(tmain.kind_sn,1)=nvl(p.kind_sn,1) and tmain.kind_code=p.kind_code and tmain.endor_no=v_endor_no;
if v_count>0 then
select mid into v_mainid
from (select tmain.id mid from UDM_P_THIRDPREMIUMTEMP tmain where tmain.premium_amount!=0  and REFLAG='N'
                                                                                              and nvl(tmain.kind_sn,1)=nvl(p.kind_sn,1) and tmain.kind_code=p.kind_code and tmain.endor_no=p.endor_no
      order by tmain.taildiff_flag,nvl(tmain.kind_sn,1),tmain.kind_code,tmain.installment_sn,tmain.coins_code,tmain.joins_code)
where rownum=1;

update UDM_P_THIRDPREMIUMTEMP tt set tt.premium_amount = tt.premium_amount-v_premium_amount,tt.extax_premium_amount = tt.extax_premium_amount-v_extax_premium_amount,
                                     tt.premium_amount_cny = tt.premium_amount_cny-v_premium_amount_cny,tt.extax_premium_amount_cny = tt.extax_premium_amount_cny-v_extax_premium_amount_cny,
                                     tt.totalpremium_amount=tt.totalpremium_amount-v_topremium_amount,
                                     tt.totalextax_premium_amount = tt.totalextax_premium_amount-v_toextax_premium_amount,
                                     tt.totalpremium_amount_cny=tt.totalpremium_amount_cny-v_topremium_amount_cny,
                                     tt.totalextax_premium_amount_cny = tt.totalextax_premium_amount_cny-v_toextax_premium_amount_cny,tt.tailDiff_flag='Y'
where tt.id = v_mainid;
end if;
end if;
end loop;
end if;

            --2.1、处理尾差--保额--以联共保表的总金额为准(剔除对冲数据)--处理主共保方
for p in (select a.endor_no,a.coins_type,a.coins_flag,a.joins_type,a.joins_flag,nvl(a.coins_code,'0') coins_code,nvl(a.joins_code,'0') joins_code,
                             sum(a.suminsured_amount) suminsured_amount,sum(a.suminsured_amount_cny) suminsured_amount_cny
                      from UDM_P_THIRDPREMIUMTEMP a where a.endor_no=v_endor_no and a.coins_flag='1'
                      group by a.endor_no,a.coins_type,a.coins_flag,a.joins_type,a.joins_flag,nvl(a.coins_code,'0'),nvl(a.joins_code,'0'))loop

select count(1) into v_count from udm_policy_main a,udm_policy_coins b
where a.id = b.main_id and a.endor_no = p.endor_no;

select count(1) into v_count2 from udm_policy_main a, udm_policy_joins c
where a.id = c.main_id and a.endor_no = p.endor_no;

if v_count > 0 and v_count2 > 0 then
select aa.joins_suminsured-p.suminsured_amount into v_suminsured_amount
from (select decode(a.coins_type,'03','0',b.coins_code) coins_code,b.coins_type,b.coins_flag,
             decode(a.joins_type,'03','0',c.joins_code) joins_code,c.joins_type,c.joins_flag,sum(c.joins_suminsured) joins_suminsured,sum(b.coins_suminsured) coins_suminsured
      from udm_policy_main a,udm_policy_coins b,udm_policy_joins c
      where a.id = b.main_id
        and a.id = c.main_id
        and a.endor_no = p.endor_no
      group by decode(a.coins_type,'03','0',b.coins_code),b.coins_type,b.coins_flag,decode(a.joins_type,'03','0',c.joins_code),c.joins_type,c.joins_flag) aa
where aa.coins_code=p.coins_code and aa.coins_type=p.coins_type and aa.coins_flag=p.coins_flag
  and aa.joins_code=p.joins_code and aa.joins_type=p.joins_type and aa.joins_flag=p.joins_flag;
end if;
                        --     DBMS_OUTPUT.put_line('尾差--'||v_suminsured_amount);
                        if v_suminsured_amount!=0 then
select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP tmain where tmain.suminsured_amount!=0  and REFLAG='N'
                                                                                             and nvl(tmain.coins_code,'0')=p.coins_code and tmain.coins_flag=p.coins_flag
                                                                                             and nvl(tmain.joins_code,'0')=p.joins_code and tmain.joins_flag=p.coins_flag;
if v_count>0 then
select mid into v_mainid
from (select tmain.id mid from UDM_P_THIRDPREMIUMTEMP tmain where tmain.suminsured_amount!=0  and REFLAG='N'
                                                                                              and nvl(tmain.coins_code,'0')=p.coins_code and tmain.coins_flag=p.coins_flag
                                                                                              and nvl(tmain.joins_code,'0')=p.joins_code and tmain.joins_flag=p.coins_flag
      order by tmain.kind_sn,tmain.installment_sn,tmain.coins_code,tmain.joins_code desc) where rownum=1;
--         DBMS_OUTPUT.put_line('尾差id--'||v_mainid);
update UDM_P_THIRDPREMIUMTEMP premtemp set premtemp.suminsured_amount=premtemp.suminsured_amount + v_suminsured_amount
where premtemp.id=v_mainid;
end if;
end if;

end loop;
            --2.1、处理尾差--保额--以联共保表的总金额为准(剔除对冲数据)--处理从共保方
for p in (select a.endor_no,a.coins_type,a.coins_flag,nvl(a.coins_code,'0') coins_code,
                             sum(a.suminsured_amount) suminsured_amount,sum(a.suminsured_amount_cny) suminsured_amount_cny
                      from UDM_P_THIRDPREMIUMTEMP a where a.endor_no=v_endor_no and a.coins_flag='0'
                      group by a.endor_no,a.coins_type,a.coins_flag,nvl(a.coins_code,'0'))loop

select count(1) into v_count from udm_policy_main a,udm_policy_coins b
where a.id = b.main_id and a.endor_no = p.endor_no and b.coins_flag=p.coins_flag
  and  decode(a.coins_type,'03','0',b.coins_code) = p.coins_code
  and b.coins_type = p.coins_type;
if v_count>0 then
select aa.coins_suminsured-p.suminsured_amount into v_suminsured_amount
from (select decode(a.coins_type,'03','0',b.coins_code) coins_code,b.coins_type,b.coins_flag,sum(b.coins_suminsured) coins_suminsured
      from udm_policy_main a,udm_policy_coins b
      where a.id = b.main_id
        and b.endor_no = p.endor_no
        and b.coins_flag=p.coins_flag
      group by decode(a.coins_type,'03','0',b.coins_code),b.coins_type,b.coins_flag) aa
where aa.coins_code=p.coins_code and aa.coins_type=p.coins_type and aa.coins_flag=p.coins_flag;
end if;
                    --     DBMS_OUTPUT.put_line('尾差22222==='||v_suminsured_amount||'====='||p.suminsured_amount);
                    if v_suminsured_amount!=0 then
select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP tmain where tmain.suminsured_amount!=0  and REFLAG='N'
                                                                                         and nvl(tmain.coins_code,'0')=p.coins_code and tmain.coins_flag=p.coins_flag;
if v_count>0 then
select mid into v_mainid
from (select tmain.id mid from UDM_P_THIRDPREMIUMTEMP tmain where tmain.suminsured_amount!=0  and REFLAG='N'
                                                                                          and nvl(tmain.coins_code,'0')=p.coins_code and tmain.coins_flag=p.coins_flag
      order by tmain.kind_sn,tmain.installment_sn,tmain.coins_code,tmain.joins_code desc) where rownum=1;
--           DBMS_OUTPUT.put_line('尾差id--'||v_mainid);
update UDM_P_THIRDPREMIUMTEMP premtemp set premtemp.suminsured_amount=premtemp.suminsured_amount + v_suminsured_amount
where premtemp.id=v_mainid;
end if;
end if;
end loop;

            --3、税额处理
update UDM_P_THIRDPREMIUMTEMP tt set tt.tax_amount=tt.premium_amount-tt.extax_premium_amount,
                                     tt.tax_amount_cny=tt.premium_amount_cny-tt.extax_premium_amount_cny,
                                     tt.totaltax_amount = tt.totalpremium_amount-tt.totalextax_premium_amount,
                                     tt.totaltax_amount_cny = tt.totalpremium_amount_cny-tt.totalextax_premium_amount_cny;
end if;

        -- select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP;
        --   DBMS_OUTPUT.put_line('保费表税额处理完成'||v_count);

Exception
        when others then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdpremiumexpand',sysdate,v_endor_no,sqlcode,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'--'||substr(sqlerrm,1,1000),'udm_p_thirdpremiumexpand');
            raise;
end;

    procedure udm_p_thirdcommfeeexpand(v_policy_no in varchar2,v_endor_no in varchar2,v_endor_type in varchar2,v_endor_sn in varchar2,v_sysday in varchar2,v_sys_source in varchar2)is
        v_mainid varchar2(32);
        v_count number(5):=0;
        v_f_count  number(5):=0;--对冲负数个数
        v_z_count  number(5):=0;--对冲正数个数
        v_exist_count number(5) := 0;
        v_fee_amount number(18,2):=0;
        v_fee_amount_cny number(18,2):=0;
        v_fee_amountold number(18,2):=0;
        v_fee_amountnew number(18,2):=0;
        v_hisdata_flag VARCHAR2(1);
begin
select count(1) into v_count from udm_policy_comm_fee where endor_no=v_endor_no and fee_rate!=0;
select t.hisdata_flag into v_hisdata_flag from UDM_P_THIRDPREMIUMTEMP t where t.endor_no=v_endor_no and rownum=1;

--是否有对冲数据
select count(1) into v_f_count from UDM_P_THIRDCOMM_FEEtemp where HEDGE_FLAG='F';
select count(1) into v_z_count from UDM_P_THIRDCOMM_FEEtemp where HEDGE_FLAG='Z';
if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 1');
end if;

        if v_f_count>0 and v_z_count = 0 and v_count>0 then
                  if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 2');
end if;
insert into UDM_P_THIRDCOMM_FEEtemp(
    id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
    operator_code,agreement_code,producer_type,producer_code,producer_name,
    is_main_producer,producer_tax_rate,fee_rate,
    fee_amount,extax_fee_amount,
    tax_amount,currency,exrate,
    fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id,
       a.policy_no 保单号,a.endor_no 批单号,a.endor_sn 批改序号,
       f.operator_code 业务员,f.agreement_code 协议号,f.producer_type 中介类型,f.producer_code 中介代码,f.producer_name 中介名称,
       f.is_main_producer 是否主代理,f.producer_tax_rate 中介税率,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate 费用汇率,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype,f.certificateno,f.confirmation_code,f.inquery_date,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,UDM_POLICY_COMM_FEE f
where a.second_main_id = f.main_id
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;

--费用批改
elsif v_endor_type in ( '1010','1040')  and 1=2 then
          if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 3');
end if;
select count(1) into v_exist_count from (
                                            select cc.id,cc.policy_no,cc.endor_sn,cc.endor_no
                                            from udm_p_thirdpremium cc where cc.policy_no=v_policy_no
                                            union all
                                            select dd.id,dd.policy_no,dd.endor_sn,dd.endor_no
                                            from udm_p_thirdpremium_Archive dd where dd.policy_no=v_policy_no) a,   (select * from UDM_P_THIRDCOMM_FEE where policy_no=v_policy_no union all select * from UDM_P_THIRDCOMM_FEE_archive where policy_no=v_policy_no) c
where c.main_id = a.id and a.policy_no=v_policy_no and a.endor_sn<v_endor_sn;
if v_exist_count > 0 then
           if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 4');
end if;
insert into udm_policy_feetemp(policy_no,coins_code,joins_code,kind_sn,kind_code,producer_code,fee_amout,fee_amout_cny)
select a.policy_no,a.coins_code,a.joins_code,a.kind_sn,a.kind_code,c.producer_code,sum(c.fee_amount),sum(c.fee_amount_cny)
from (select cc.id,cc.policy_no,cc.endor_sn,cc.coins_code,cc.joins_code,cc.endor_no,cc.kind_sn,cc.kind_code
      from udm_p_thirdpremium cc where cc.policy_no=v_policy_no
      union all
      select dd.id,dd.policy_no,dd.endor_sn,dd.coins_code,dd.joins_code,dd.endor_no,dd.kind_sn,dd.kind_code
      from udm_p_thirdpremium_Archive dd where dd.policy_no=v_policy_no) a,
     (select cc.id,cc.main_id,cc.policy_no,cc.endor_no,cc.producer_code,cc.fee_amount,cc.fee_amount_cny
      from udm_p_thirdcomm_fee cc where cc.policy_no=v_policy_no
      union all
      select dd.id,dd.main_id,dd.policy_no,dd.endor_no,dd.producer_code,dd.fee_amount,dd.fee_amount_cny
      from udm_p_thirdcomm_fee_Archive dd where dd.policy_no=v_policy_no) c
where c.main_id = a.id
  and a.policy_no=v_policy_no
  and a.endor_sn<v_endor_sn
group by a.policy_no,a.coins_code,a.joins_code,a.kind_sn,a.kind_code,c.producer_code;
if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 5');
end if;
insert into UDM_P_THIRDCOMM_FEEtemp(
    id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
    operator_code,agreement_code,producer_type,producer_code,producer_name,
    is_main_producer,producer_tax_rate,fee_rate,
    fee_amount,extax_fee_amount,tax_amount,currency,exrate,
    fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id,
       a.policy_no 保单号,a.endor_no 批单号,a.endor_sn 批改序号,
       f.operator_code 业务员,f.agreement_code 协议号,f.producer_type 中介类型,f.producer_code 中介代码,f.producer_name 中介名称,
       f.is_main_producer 是否主代理,f.producer_tax_rate 中介税率,f.fee_rate 费用比例,
       --费用金额变化量=最新责任保费*最新费用比例-历史费用金额变化量合计
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate-tempfee.fee_amout,2)
            else round(a.totalpremium_amount*f.fee_rate-tempfee.fee_amout,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate-tempfee.fee_amout,2)
            else round(a.totalpremium_amount*f.fee_rate-tempfee.fee_amout,2)
           end as 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,f.currency,f.tax_rate,
       case when f.ccaltype='0' then round((a.totalextax_premium_amount*f.fee_rate-tempfee.fee_amout)*f.tax_rate,2)
            else round((a.totalpremium_amount*f.fee_rate-tempfee.fee_amout)*f.tax_rate,2)
           end as 费用金额变化量本位币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate-tempfee.fee_amout_cny,2)
            else round(a.totalpremium_amount_cny*f.fee_rate-tempfee.fee_amout_cny,2)
           end as 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype,f.certificateno,f.confirmation_code,f.inquery_date,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,UDM_POLICY_COMM_FEE f,udm_policy_feetemp tempfee
where a.second_main_id = f.main_id
  and nvl(a.coins_code,'0')=nvl(tempfee.coins_code,'0')
  and nvl(a.joins_code,'0') = nvl(tempfee.joins_code,'0')
  and f.producer_code = tempfee.producer_code
  and nvl(a.kind_code,0) = nvl(tempfee.kind_code,0)
  and nvl(a.kind_sn,0) = nvl(tempfee.kind_sn,0)
  and a.policy_no = tempfee.policy_no
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;

--判断是否有新增的代理
select count(1) into v_count from udm_policy_comm_fee f
where not exists (select 1 from (select cc.endor_no,cc.policy_no,cc.producer_code from udm_p_thirdcomm_fee cc where cc.policy_no=v_policy_no
                                 union all
                                 select dd.endor_no,dd.policy_no,dd.producer_code from udm_p_thirdcomm_fee_Archive dd where dd.policy_no=v_policy_no) b where b.policy_no=f.policy_no and b.producer_code=f.producer_code)
  and f.endor_no = v_endor_no;
if v_count > 0 then
                          if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 6');
end if;
insert into UDM_P_THIRDCOMM_FEEtemp(
    id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
    operator_code,agreement_code,producer_type,producer_code,producer_name,
    is_main_producer,producer_tax_rate,fee_rate,
    fee_amount,extax_fee_amount,
    tax_amount,currency,exrate,
    fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id,
       a.policy_no 保单号,a.endor_no 批单号,a.endor_sn 批改序号,
       f.operator_code 业务员,f.agreement_code 协议号,f.producer_type 中介类型,f.producer_code 中介代码,f.producer_name 中介名称,
       f.is_main_producer 是否主代理,f.producer_tax_rate 中介税率,f.fee_rate 费用比例,
       --费用金额变化量=最新责任保费*最新费用比例
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate 费用汇率,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype,f.certificateno,f.confirmation_code,f.inquery_date,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,UDM_POLICY_COMM_FEE f
where a.second_main_id = f.main_id
  and not exists (select 1 from UDM_P_THIRDCOMM_FEE b where b.policy_no=f.policy_no and b.producer_code=f.producer_code)
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
end if;

else
                      if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 7');
end if;
insert into UDM_P_THIRDCOMM_FEEtemp(
    id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
    operator_code,agreement_code,producer_type,producer_code,producer_name,
    is_main_producer,producer_tax_rate,fee_rate,
    fee_amount,extax_fee_amount,
    tax_amount,currency,exrate,
    fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id,
       a.policy_no 保单号,a.endor_no 批单号,a.endor_sn 批改序号,
       f.operator_code 业务员,f.agreement_code 协议号,f.producer_type 中介类型,f.producer_code 中介代码,f.producer_name 中介名称,
       f.is_main_producer 是否主代理,f.producer_tax_rate 中介税率,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate 费用汇率,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype,f.certificateno,f.confirmation_code,f.inquery_date,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,UDM_POLICY_COMM_FEE f
where a.second_main_id = f.main_id
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
end if;

        elsif v_count>0 then
                  if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 8');
end if;
            --1.2.其他
insert into UDM_P_THIRDCOMM_FEEtemp(
    id,Main_id,SECOND_ID,policy_no,endor_no,endor_sn,
    operator_code,agreement_code,producer_type,producer_code,producer_name,
    is_main_producer,producer_tax_rate,fee_rate,
    fee_amount,extax_fee_amount,
    tax_amount,currency,exrate,
    fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
    ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tcommfeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id, a.policy_no 保单号,a.endor_no 批单号,a.endor_sn 批改序号,
       f.operator_code 业务员,f.agreement_code 协议号,f.producer_type 中介类型,f.producer_code 中介代码,f.producer_name 中介名称,
       f.is_main_producer 是否主代理,f.producer_tax_rate 中介税率,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.extax_premium_amount*f.fee_rate,2)
            else round(a.premium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round(a.extax_premium_amount*f.fee_rate,2)
            else round(a.premium_amount*f.fee_rate,2)
           end as 不含税费用金额变化量交易币,
       0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate 费用汇率,
       case when f.ccaltype='0' then round(a.extax_premium_amount_cny*f.fee_rate,2)
            else round(a.premium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       case when f.ccaltype='0' then round(a.extax_premium_amount_cny*f.fee_rate,2)
            else round(a.premium_amount_cny*f.fee_rate,2)
           end as 不含税费用金额变化量本位币,
       0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,f.certificateno 中介机构许可证号个人代理资格证号,
       f.confirmation_code 投保批改确认码,f.inquery_date 投保查询时间,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,UDM_POLICY_COMM_FEE f
where a.second_main_id = f.main_id
  and a.policy_no = f.policy_no
  and a.endor_no = f.endor_no
  and a.reflag = 'N'
  and a.policy_no=v_policy_no and a.endor_no=v_endor_no;

end if;


select count(1) into v_count from UDM_P_THIRDCOMM_FEEtemp a where a.hedge_flag='N';

if v_count>0 and 1=2 then
                  if v_endor_no='CS172324W02100000004E01' then
          DBMS_OUTPUT.put_line('udm_p_thirdcommfeeexpand 9');
end if;
            --2、尾差处理(到代理人)
            if v_f_count>0 and v_z_count = 0 then
                --只冲负，冲正重新算的情况，尾差按最新值算
                for p in (select distinct policy_no,endor_no,producer_code from UDM_P_THIRDCOMM_FEEtemp where hedge_flag='N' and endor_no=v_endor_no)loop
select (aa.newfee_amount-bb.oldfee_amount) fee_amount,(aa.newfee_amount_cny-bb.oldfee_amount_cny) fee_amount_cny
into v_fee_amount,v_fee_amount_cny from
                                       (select sum(a.fee_amount) newfee_amount,sum(a.fee_amount_cny) newfee_amount_cny
                                        from UDM_P_THIRDCOMM_FEEtemp a where a.hedge_flag='N' and a.producer_code=p.producer_code and a.endor_no=p.endor_no ) aa,
                                       (select sum(a.fee_amount) oldfee_amount,sum(a.fee_amount_cny) oldfee_amount_cny
                                        from UDM_POLICY_COMM_FEE a,udm_policy_main b where a.endor_no = b.endor_no and b.endor_sn<=v_endor_sn
                                                                                       and a.policy_no=p.policy_no and a.producer_code=p.producer_code) bb ;

--DBMS_OUTPUT.put_line('weicha -'||p.producer_code||'-'||to_char(v_fee_amount)||'-'||to_char(v_fee_amount_cny));

if v_fee_amount != 0 or v_fee_amount_cny!=0 then
select count(1) into v_count from UDM_P_THIRDCOMM_FEEtemp feetemp where feetemp.endor_no = p.endor_no
                                                                    and feetemp.fee_amount!=0
                                                                                                and feetemp.hedge_flag='N' and feetemp.producer_code=p.producer_code;
if v_count>0 then
select mid into v_mainid
from (select feetemp.id mid from UDM_P_THIRDCOMM_FEEtemp feetemp where feetemp.endor_no = v_endor_no and feetemp.fee_amount!=0
                                                                                                   and feetemp.hedge_flag='N' and feetemp.producer_code=p.producer_code order by feetemp.id desc)
where rownum=1;

update UDM_P_THIRDCOMM_FEEtemp tt set tt.fee_amount = tt.fee_amount-v_fee_amount,
                                      tt.fee_amount_cny = tt.fee_amount_cny-v_fee_amount_cny,tt.taildiff_flag='Y'
where tt.id = v_mainid;
end if;
end if;
end loop;

else
                for p in (select distinct endor_no,producer_code from UDM_P_THIRDCOMM_FEEtemp where hedge_flag='N' and endor_no=v_endor_no)loop
                        --到批单粒度 新批单手续费用总和-二层手续费，即尾差
select (aa.newfee_amount-bb.oldfee_amount) fee_amount,(aa.newfee_amount_cny-bb.oldfee_amount_cny) fee_amount_cny
into v_fee_amount,v_fee_amount_cny from
                                       (select sum(a.fee_amount) newfee_amount,sum(a.fee_amount_cny) newfee_amount_cny
                                        from UDM_P_THIRDCOMM_FEEtemp a where a.hedge_flag='N' and a.producer_code=p.producer_code and a.endor_no=p.endor_no ) aa,
                                       (select sum(a.fee_amount) oldfee_amount,sum(a.fee_amount_cny) oldfee_amount_cny
                                        from UDM_POLICY_COMM_FEE a where a.endor_no=p.endor_no and a.producer_code=p.producer_code) bb ;

if v_fee_amount != 0 or v_fee_amount_cny!=0 then
select count(1) into v_count from UDM_P_THIRDCOMM_FEEtemp feetemp where feetemp.endor_no = p.endor_no
                                                                    and feetemp.fee_amount!=0
                                                                                                and feetemp.hedge_flag='N' and feetemp.producer_code=p.producer_code;
if v_count>0 then
                                --任取批单一个费用
select mid into v_mainid
from (select feetemp.id mid from UDM_P_THIRDCOMM_FEEtemp feetemp where feetemp.endor_no = v_endor_no and feetemp.fee_amount!=0
                                                                                                   and feetemp.hedge_flag='N' and feetemp.producer_code=p.producer_code order by feetemp.id desc)
where rownum=1;

update UDM_P_THIRDCOMM_FEEtemp tt set tt.fee_amount = tt.fee_amount-v_fee_amount,
                                      tt.fee_amount_cny = tt.fee_amount_cny-v_fee_amount_cny,tt.taildiff_flag='Y'
where tt.id = v_mainid;
end if;
end if;
end loop;
end if;

            if v_hisdata_flag='1' then --历史数据不拆，不含税费用金额变化量=费用金额变化量
                --不含税费用金额变化量 = 费用金额变化量
update UDM_P_THIRDCOMM_FEEtemp a set a.extax_fee_amount=a.fee_amount,a.extax_fee_amount_cny=a.fee_amount_cny
where a.endor_no = v_endor_no;
else
                --3、价税拆分 冲减的、免税责任无需拆分；0税率的,不含税=含税
update UDM_P_THIRDCOMM_FEEtemp feetemp set feetemp.tax_amount=round(feetemp.fee_amount*feetemp.producer_tax_rate/(1+feetemp.producer_tax_rate),2),
                                           feetemp.tax_amount_cny=round(feetemp.fee_amount_cny*feetemp.producer_tax_rate/(1+feetemp.producer_tax_rate),2)
where feetemp.hedge_flag='N' and feetemp.producer_tax_rate!=0
                  and not exists (select 1 from UDM_P_THIRDPREMIUMTEMP premiumtemp where premiumtemp.id=feetemp.main_id and premiumtemp.taxfree_flag='1');

update UDM_P_THIRDCOMM_FEEtemp feetemp set feetemp.extax_fee_amount = feetemp.fee_amount-feetemp.tax_amount,
                                           feetemp.extax_fee_amount_cny = feetemp.fee_amount_cny-feetemp.tax_amount_cny where feetemp.hedge_flag='N';
--   DBMS_OUTPUT.put_line('commfee表价税拆分');
end if;
end if;

Exception
        when others then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdcommfeeexpand',sysdate,v_endor_no,sqlcode,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'--'||substr(sqlerrm,1,1000),'udm_p_thirdcommfeeexpand');
            raise;
end;

    procedure udm_p_thirdsalefeeexpand(v_policy_no in varchar2,v_endor_no in varchar2,v_endor_type in varchar2,v_endor_sn in varchar2,v_sysday in varchar2,v_sys_source in varchar2)is
        v_mainid varchar2(32);
        v_count number(6):=0;
        v_exist_count number(6):=0;
        v_f_count  number(6):=0;--对冲负数个数
        v_z_count  number(6):=0;--对冲正数个数
        v_fee_amount number(18,2):=0;
        v_fee_amount_cny number(18,2):=0;
        v_fee_amountold number(18,2):=0;
        v_fee_amountnew number(18,2):=0;
begin

select count(1) into v_count from udm_policy_sales_fee where endor_no=v_endor_no and fee_rate!=0;
--是否有对冲数据
select count(1) into v_f_count from UDM_P_THIRDSALES_FEEtemp where HEDGE_FLAG='F';
select count(1) into v_z_count from UDM_P_THIRDSALES_FEEtemp where HEDGE_FLAG='Z';

if v_f_count>0 and v_z_count=0 and v_count>0 then
            insert into UDM_P_THIRDSALES_FEEtemp(
                id,main_id, second_id, policy_no,endor_no,crct_no,
                fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
                tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
                tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
                hedge_thirdid,taildiff_flag,HEDGE_FLAG
            )
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id,
       a.policy_no 保单号,a.endor_no 批单号,f.crct_no,
       f.fee_type,f.operator_code 业务员,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate 税率,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,f.is_fixed_rate,null,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
--费用批改
elsif v_endor_type in ( '1010','1040') then
select count(1) into v_exist_count from
                                       (select cc.policy_no,cc.endor_sn,cc.id
                                        from udm_p_thirdpremium cc where cc.policy_no=v_policy_no
                                        union all
                                        select dd.policy_no,dd.endor_sn,dd.id
                                        from udm_p_thirdpremium_archive dd where dd.policy_no=v_policy_no) a,
                                       (select cc.main_id from udm_p_thirdsales_fee cc where cc.policy_no=v_policy_no
                                        union all
                                        select dd.main_id from udm_p_thirdsales_fee_archive dd where dd.policy_no=v_policy_no
                                       ) c
where c.main_id = a.id and a.policy_no=v_policy_no and a.endor_sn<v_endor_sn;
if v_exist_count>0 then
                insert into udm_policy_salesfeetemp(policy_no,coins_code,joins_code,kind_sn,kind_code,OPERATOR_CODE,fee_type,fee_amout,fee_amout_cny)
select a.policy_no,a.coins_code,a.joins_code,a.kind_sn,a.kind_code,c.OPERATOR_CODE,c.fee_type,sum(c.fee_amount),sum(c.fee_amount_cny)
from (select cc.policy_no,cc.endor_sn,cc.endor_no,cc.id,cc.coins_code,cc.joins_code,cc.kind_sn,cc.kind_code
      from udm_p_thirdpremium cc where cc.policy_no=v_policy_no
      union all
      select dd.policy_no,dd.endor_sn,dd.endor_no,dd.id,dd.coins_code,dd.joins_code,dd.kind_sn,dd.kind_code
      from udm_p_thirdpremium_archive dd where dd.policy_no=v_policy_no) a,
     (select cc.main_id,cc.operator_code,cc.fee_type,cc.fee_amount,cc.fee_amount_cny from udm_p_thirdsales_fee cc where cc.policy_no=v_policy_no
      union all
      select dd.main_id,dd.operator_code,dd.fee_type,dd.fee_amount,dd.fee_amount_cny from udm_p_thirdsales_fee_archive dd where dd.policy_no=v_policy_no
     ) c
where c.main_id = a.id
  and a.policy_no=v_policy_no
  -- and c.hedge_thirdid is null and not exists (select 1 from UDM_P_THIRDSALES_FEE d where d.hedge_thirdid=c.id and d.hedge_thirdid is not null)
  and a.endor_sn<v_endor_sn
group by a.policy_no,a.coins_code,a.joins_code,a.kind_sn,a.kind_code,c.OPERATOR_CODE,c.fee_type;

select count(1) into v_count from udm_policy_salesfeetemp;
UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'1-历史数据个数='||to_char(v_count)||'='||v_endor_sn,'sales');

select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP a,udm_policy_sales_fee f
where a.second_main_id = f.main_id and a.reflag = 'N'
  and f.endor_no = v_endor_no;
UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'2-新数据个数='||to_char(v_count),'sales');

insert into UDM_P_THIRDSALES_FEEtemp(
    id,main_id, second_id, policy_no,endor_no,crct_no,
    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
    hedge_thirdid,taildiff_flag,HEDGE_FLAG
)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id, a.policy_no 保单号,a.endor_no 批单号,f.crct_no,
       f.fee_type,f.operator_code 业务员,f.fee_rate 费用比例,
       --费用金额变化量=最新责任保费*最新费用比例-历史费用金额变化量合计
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate-tempfee.fee_amout,2)
            else round(a.totalpremium_amount*f.fee_rate-tempfee.fee_amout,2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate 税率,
       case when f.ccaltype='0' then round((a.totalextax_premium_amount*f.fee_rate-tempfee.fee_amout)*a.premium_exrate,2)
            else round((a.totalpremium_amount*f.fee_rate-tempfee.fee_amout)*a.premium_exrate,2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,f.is_fixed_rate,null,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_sales_fee f,udm_policy_salesfeetemp tempfee
where a.second_main_id = f.main_id
  and nvl(a.coins_code,'0')=nvl(tempfee.coins_code,'0')
  and nvl(a.joins_code,'0') = nvl(tempfee.joins_code,'0')
  and f.operator_code = tempfee.operator_code
  and nvl(a.kind_code,0) = nvl(tempfee.kind_code,0)
  and nvl(a.kind_sn,0) = nvl(tempfee.kind_sn,0)
  and a.policy_no = tempfee.policy_no
  and f.fee_type = tempfee.fee_type
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;

select count(1) into v_count from UDM_P_THIRDSALES_FEEtemp;
UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'3-最新数据个数='||to_char(v_count),'sales');

                --判断是否有新增的费用类型
select count(1) into v_count from udm_policy_sales_fee f
where not exists (select 1 from (select cc.policy_no,cc.fee_type from udm_p_thirdsales_fee cc where cc.policy_no=v_policy_no
                                 union all select dd.policy_no,dd.fee_type from udm_p_thirdsales_fee_archive dd where dd.policy_no=v_policy_no) b
                  where b.policy_no=f.policy_no and b.fee_type=f.fee_type)
  and f.endor_no = v_endor_no;
if v_count > 0 then
                    insert into UDM_P_THIRDSALES_FEEtemp(
                        id,main_id, second_id, policy_no,endor_no,crct_no,
                        fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
                        tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
                        tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
                        hedge_thirdid,taildiff_flag,HEDGE_FLAG
                    )
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id,
       a.policy_no 保单号,a.endor_no 批单号,f.crct_no,
       f.fee_type,f.operator_code 业务员,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate 税率,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,f.is_fixed_rate,null,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and not exists (select 1 from (select cc.policy_no,cc.fee_type from udm_p_thirdsales_fee cc where cc.policy_no=v_policy_no
                                 union all select dd.policy_no,dd.fee_type from udm_p_thirdsales_fee_archive dd where dd.policy_no=v_policy_no) b
                  where b.policy_no=f.policy_no and b.fee_type=f.fee_type)
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
end if;

else
                insert into UDM_P_THIRDSALES_FEEtemp(
                    id,main_id, second_id, policy_no,endor_no,crct_no,
                    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
                    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
                    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
                    hedge_thirdid,taildiff_flag,HEDGE_FLAG
                )
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id,
       a.policy_no 保单号,a.endor_no 批单号,f.crct_no,
       f.fee_type,f.operator_code 业务员,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币,0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate 税率,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,f.is_fixed_rate,null,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
end if;

        elsif v_count>0 then
            --1.2.其他
            insert into UDM_P_THIRDSALES_FEEtemp(
                id,main_id, second_id, policy_no,endor_no,crct_no,
                fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
                tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
                tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
                hedge_thirdid,taildiff_flag,HEDGE_FLAG
            )
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalefeeid.Nextval, 12, '0') ID,a.id Main_ID,f.id, a.policy_no 保单号,a.endor_no 批单号,f.crct_no,
       f.fee_type,f.operator_code,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.extax_premium_amount*f.fee_rate,2)
            else round(a.premium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       0 不含税费用金额变化量交易币, 0 费用税额变化量交易币,
       f.currency 费用币种,f.tax_rate,
       case when f.ccaltype='0' then round(a.extax_premium_amount_cny*f.fee_rate,2)
            else round(a.premium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       0 不含税费用金额变化量本位币,0 费用税额变化量本位币,
       f.ccaltype 含税保费计算方式,f.is_fixed_rate,null,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_sales_fee f
where a.second_main_id = f.main_id
  and a.policy_no = f.policy_no
  and a.endor_no = f.endor_no
  and a.reflag = 'N'
  and a.policy_no=v_policy_no and a.endor_no=v_endor_no;
end if;

select count(1) into v_count from UDM_P_THIRDSALES_FEEtemp a where a.hedge_flag='N' and a.endor_no=v_endor_no;

--DBMS_OUTPUT.put_line('salesfee非对冲条数'||v_count);
if v_count>0 and 1=2 then
            --2、尾差处理,需要按照费用类型进行尾差处理
            if v_f_count>0 and v_z_count = 0 then
                --只冲负，冲正重新算的情况，尾差按最新值算
                for p in (select distinct a.policy_no,a.endor_no,a.fee_type from UDM_P_THIRDSALES_FEEtemp a where a.hedge_flag='N' and a.endor_no=v_endor_no)loop
select (aa.newfee_amount-bb.oldfee_amount) fee_amount,(aa.newfee_amount_cny-bb.oldfee_amount_cny) fee_amount_cny
into v_fee_amount,v_fee_amount_cny
from
    (select sum(a.fee_amount) newfee_amount,sum(a.fee_amount_cny) newfee_amount_cny
     from UDM_P_THIRDSALES_FEEtemp a where a.hedge_flag='N' and a.endor_no=p.endor_no and a.fee_type=p.fee_type
     group by a.endor_no) aa,
    (select sum(a.fee_amount) oldfee_amount,sum(a.fee_amount_cny) oldfee_amount_cny
     from Udm_Policy_Sales_Fee a,udm_policy_main b
     where a.endor_no=b.endor_no and b.endor_sn<=v_endor_sn and a.policy_no=p.policy_no and a.fee_type=p.fee_type group by a.policy_no) bb ;

if v_fee_amount != 0 or v_fee_amount_cny !=0 then
select count(1) into v_count from UDM_P_THIRDSALES_FEEtemp feetemp
where feetemp.endor_no = p.endor_no
  and feetemp.fee_type = p.fee_type and feetemp.fee_amount!=0
                              and feetemp.hedge_flag='N';
if v_count>0 then
select mid into v_mainid
from (select feetemp.id mid from UDM_P_THIRDSALES_FEEtemp feetemp
      where feetemp.endor_no = p.endor_no
        and feetemp.fee_type = p.fee_type and feetemp.fee_amount!=0
                                        and feetemp.hedge_flag='N' order by feetemp.id desc)
where rownum=1;

update UDM_P_THIRDSALES_FEEtemp tt set tt.fee_amount = tt.fee_amount-v_fee_amount,
                                       tt.fee_amount_cny = tt.fee_amount_cny-v_fee_amount_cny,tt.taildiff_flag='Y'
where tt.id = v_mainid;
end if;
end if;
end loop;
else

                for p in (select distinct a.endor_no,a.fee_type from UDM_P_THIRDSALES_FEEtemp a where a.hedge_flag='N' and a.endor_no=v_endor_no)loop
select (aa.newfee_amount-bb.oldfee_amount) fee_amount,(aa.newfee_amount_cny-bb.oldfee_amount_cny) fee_amount_cny
into v_fee_amount,v_fee_amount_cny
from
    (select sum(a.fee_amount) newfee_amount,sum(a.fee_amount_cny) newfee_amount_cny
     from UDM_P_THIRDSALES_FEEtemp a where a.hedge_flag='N' and a.endor_no=p.endor_no and a.fee_type=p.fee_type
     group by a.endor_no) aa,
    (select sum(a.fee_amount) oldfee_amount,sum(a.fee_amount_cny) oldfee_amount_cny
     from Udm_Policy_Sales_Fee a where a.endor_no=p.endor_no and a.fee_type=p.fee_type group by a.endor_no) bb ;

if v_fee_amount != 0 or v_fee_amount_cny !=0 then
select count(1) into v_count from UDM_P_THIRDSALES_FEEtemp feetemp
where feetemp.endor_no = p.endor_no
  and feetemp.fee_type = p.fee_type and feetemp.fee_amount!=0
                              and feetemp.hedge_flag='N';
if v_count>0 then
select mid into v_mainid
from (select feetemp.id mid from UDM_P_THIRDSALES_FEEtemp feetemp
      where feetemp.endor_no = p.endor_no
        and feetemp.fee_type = p.fee_type and feetemp.fee_amount!=0
                                        and feetemp.hedge_flag='N' order by feetemp.id desc)
where rownum=1;

update UDM_P_THIRDSALES_FEEtemp tt set tt.fee_amount = tt.fee_amount-v_fee_amount,
                                       tt.fee_amount_cny = tt.fee_amount_cny-v_fee_amount_cny,tt.taildiff_flag='Y'
where tt.id = v_mainid;
end if;
end if;
end loop;

end if;
end if;

        --统一处理：不含税费用金额变化量 = 费用金额变化量
update UDM_P_THIRDSALES_FEEtemp a set a.extax_fee_amount=a.fee_amount,a.extax_fee_amount_cny=a.fee_amount_cny
where a.endor_no = v_endor_no;


Exception
        when others then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdsalefeeexpand',sysdate,v_endor_no,sqlcode,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'--'||substr(sqlerrm,1,1000),'udm_p_thirdsalefeeexpand');
            raise;
end;

    procedure udm_p_thirdsalaryexpand(v_policy_no in varchar2,v_endor_no in varchar2,v_endor_type in varchar2,v_endor_sn in varchar2,v_sysday in varchar2,v_sys_source in varchar2)is
        v_mainid varchar2(32);
        v_count number(5):=0;
        v_exist_count number(5):=0;
        v_f_count  number(5):=0;--对冲负数个数
        v_z_count  number(5):=0;--对冲正数个数
        v_fee_amount number(18,2):=0;
        v_fee_amount_cny number(18,2):=0;
        v_fee_amountold number(18,2):=0;
        v_fee_amountnew number(18,2):=0;
begin
select count(1) into v_count from udm_policy_salary where endor_no=v_endor_no and fee_rate!=0;
--是否有对冲数据
select count(1) into v_f_count from UDM_P_THIRD_SALARYTEMP where HEDGE_FLAG='F';
select count(1) into v_z_count from UDM_P_THIRD_SALARYTEMP where HEDGE_FLAG='Z';

if v_f_count>0 and v_z_count=0 and v_count>0 then
            insert into UDM_P_THIRD_SALARYTEMP(
                id,main_id,second_id,policy_no ,endor_no,
                crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
                ccaltype,hedge_sencondid,hedge_thirdid,hedge_flag
            )
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalaryid.Nextval, 12, '0') ID,a.id Main_ID,f.id, a.policy_no 保单号,a.endor_no 批单号,
       f.crct_no,f.operator_code 业务员,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       f.ccaltype 含税保费计算方式,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_salary f
where a.second_main_id = f.main_id
  and a.policy_no = f.policy_no
  and a.endor_no = f.endor_no
  and a.reflag = 'N'
  and a.policy_no=v_policy_no and a.endor_no=v_endor_no;
--费用批改
elsif v_endor_type in ( '1010','1040') then
select count(1) into v_exist_count from
                                       (select cc.policy_no,cc.endor_sn,cc.id
                                        from udm_p_thirdpremium cc where cc.policy_no=v_policy_no
                                        union all
                                        select dd.policy_no,dd.endor_sn,dd.id
                                        from udm_p_thirdpremium_archive dd where dd.policy_no=v_policy_no) a,
                                       (select cc.main_id,cc.policy_no from udm_p_third_salary cc where cc.policy_no=v_policy_no
                                        union all select dd.main_id,dd.policy_no from udm_p_third_salary_archive dd where dd.policy_no=v_policy_no) c
where c.main_id = a.id and a.policy_no=v_policy_no and a.endor_sn<v_endor_sn;
if v_exist_count >0 then
                insert into udm_policy_salarytemp(policy_no,coins_code,joins_code,kind_sn,kind_code,operator_code,fee_amout,fee_amout_cny)
select a.policy_no,a.coins_code,a.joins_code,a.kind_sn,a.kind_code,c.operator_code,sum(c.fee_amount),sum(c.fee_amount_cny)
from (select cc.policy_no,cc.endor_sn,cc.coins_code,cc.joins_code,cc.kind_sn,cc.kind_code,cc.id
      from udm_p_thirdpremium cc where cc.policy_no=v_policy_no
      union all
      select dd.policy_no,dd.endor_sn,dd.coins_code,dd.joins_code,dd.kind_sn,dd.kind_code,dd.id
      from udm_p_thirdpremium_archive dd where dd.policy_no=v_policy_no) a,
     (select cc.main_id,cc.policy_no,cc.operator_code,cc.fee_amount,cc.fee_amount_cny from udm_p_third_salary cc where cc.policy_no=v_policy_no
      union all select dd.main_id,dd.policy_no,dd.operator_code,dd.fee_amount,dd.fee_amount_cny from udm_p_third_salary_archive dd where dd.policy_no=v_policy_no) c
where c.main_id = a.id
  and a.policy_no=v_policy_no
  and a.endor_sn<v_endor_sn
group by a.policy_no,a.coins_code,a.joins_code,a.kind_sn,a.kind_code,c.operator_code;

insert into UDM_P_THIRD_SALARYTEMP(
    id,main_id,second_id,policy_no ,endor_no,
    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
    ccaltype,hedge_sencondid,hedge_thirdid,hedge_flag
)
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalaryid.Nextval, 12, '0') ID,a.id Main_ID,f.id, a.policy_no 保单号,a.endor_no 批单号,
       f.crct_no,f.operator_code 业务员,f.fee_rate 费用比例,
       --费用金额变化量=最新责任保费*最新费用比例-历史费用金额变化量合计
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate-tempfee.fee_amout,2)
            else round(a.totalpremium_amount*f.fee_rate-tempfee.fee_amout,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round((a.totalextax_premium_amount*f.fee_rate-tempfee.fee_amout)*a.premium_exrate,2)
            else round((a.totalpremium_amount*f.fee_rate-tempfee.fee_amout)*a.premium_exrate,2)
           end as 费用金额变化量本位币,
       f.ccaltype 含税保费计算方式,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_salary f,udm_policy_salarytemp tempfee
where a.second_main_id = f.main_id
  and nvl(a.coins_code,'0') = nvl(tempfee.coins_code,'0')
  and nvl(a.joins_code,'0') = nvl(tempfee.joins_code,'0')
  and f.operator_code = tempfee.operator_code
  and nvl(a.kind_code,0) = nvl(tempfee.kind_code,0)
  and nvl(a.kind_sn,0) = nvl(tempfee.kind_sn,0)
  and a.policy_no = tempfee.policy_no
  and a.reflag = 'N'
  and f.endor_no = v_endor_no;
else
                insert into UDM_P_THIRD_SALARYTEMP(
                    id,main_id,second_id,policy_no ,endor_no,
                    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
                    ccaltype,hedge_sencondid,hedge_thirdid,hedge_flag
                )
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalaryid.Nextval, 12, '0') ID,a.id Main_ID,f.id, a.policy_no 保单号,a.endor_no 批单号,
       f.crct_no,f.operator_code 业务员,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount*f.fee_rate,2)
            else round(a.totalpremium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round(a.totalextax_premium_amount_cny*f.fee_rate,2)
            else round(a.totalpremium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       f.ccaltype 含税保费计算方式,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_salary f
where a.second_main_id = f.main_id
  and a.policy_no = f.policy_no
  and a.endor_no = f.endor_no
  and a.reflag = 'N'
  and a.policy_no=v_policy_no and a.endor_no=v_endor_no;
end if;

        elsif v_count>0 then
            --1.2.其他
            insert into UDM_P_THIRD_SALARYTEMP(
                id,main_id,second_id,policy_no ,endor_no,
                crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
                ccaltype,hedge_sencondid,hedge_thirdid,hedge_flag
            )
select v_sys_source||v_sysday||LPAD(SEQ_UDM_P_Tsalaryid.Nextval, 12, '0') ID,a.id Main_ID,f.id, a.policy_no 保单号,a.endor_no 批单号,
       f.crct_no,f.operator_code 业务员,f.fee_rate 费用比例,
       case when f.ccaltype='0' then round(a.extax_premium_amount*f.fee_rate,2)
            else round(a.premium_amount*f.fee_rate,2)
           end as 费用金额变化量交易币,
       case when f.ccaltype='0' then round(a.extax_premium_amount_cny*f.fee_rate,2)
            else round(a.premium_amount_cny*f.fee_rate,2)
           end as 费用金额变化量本位币,
       f.ccaltype 含税保费计算方式,null,null,'N'
from UDM_P_THIRDPREMIUMTEMP a,udm_policy_salary f
where a.second_main_id = f.main_id
  and a.policy_no = f.policy_no
  and a.endor_no = f.endor_no
  and a.reflag = 'N'
  and a.policy_no=v_policy_no and a.endor_no=v_endor_no;

end if;

select count(1) into v_count from UDM_P_THIRD_SALARYTEMP a where a.hedge_flag='N';
--2、尾差处理
if v_count>0 and 1=2 then
            if v_f_count>0 and v_z_count=0 then
select (aa.newfee_amount-bb.oldfee_amount) fee_amount,(aa.newfee_amount_cny-bb.oldfee_amount_cny) fee_amount_cny
into v_fee_amount,v_fee_amount_cny from
                                       (select sum(a.fee_amount) newfee_amount,sum(a.fee_amount_cny) newfee_amount_cny
                                        from UDM_P_THIRD_SALARYTEMP a where a.hedge_flag='N' group by a.endor_no ) aa,
                                       (select sum(a.fee_amount) oldfee_amount,sum(a.fee_amount_cny) oldfee_amount_cny
                                        from udm_policy_salary a,udm_policy_main b
                                        where a.endor_no=b.endor_no and b.endor_sn<=v_endor_sn and a.policy_no=v_policy_no group by a.policy_no) bb ;
if v_fee_amount != 0 then
select count(1) into v_count from UDM_P_THIRD_SALARYTEMP feetemp where feetemp.endor_no = v_endor_no and feetemp.fee_amount!=0
                                                                                       and feetemp.hedge_flag='N';
if v_count>0 then
select mid into v_mainid
from (select feetemp.id mid from UDM_P_THIRD_SALARYTEMP feetemp where feetemp.endor_no = v_endor_no and feetemp.fee_amount!=0
                                                                                          and feetemp.hedge_flag='N' order by feetemp.id desc)
where rownum=1;

update UDM_P_THIRD_SALARYTEMP tt set tt.fee_amount = tt.fee_amount-v_fee_amount,
                                     tt.fee_amount_cny = tt.fee_amount_cny-v_fee_amount_cny,tt.taildiff_flag='Y'
where tt.id = v_mainid;
end if;
end if;

else
select (aa.newfee_amount-bb.oldfee_amount) fee_amount,(aa.newfee_amount_cny-bb.oldfee_amount_cny) fee_amount_cny
into v_fee_amount,v_fee_amount_cny from
                                       (select sum(a.fee_amount) newfee_amount,sum(a.fee_amount_cny) newfee_amount_cny
                                        from UDM_P_THIRD_SALARYTEMP a where a.hedge_flag='N' group by a.endor_no ) aa,
                                       (select sum(a.fee_amount) oldfee_amount,sum(a.fee_amount_cny) oldfee_amount_cny
                                        from udm_policy_salary a where a.endor_no=v_endor_no group by a.endor_no) bb ;
if v_fee_amount != 0 or v_fee_amount_cny!=0 then
select count(1) into v_count from UDM_P_THIRD_SALARYTEMP feetemp where feetemp.endor_no = v_endor_no and feetemp.fee_amount!=0
                                                                                       and feetemp.hedge_flag='N';
if v_count>0 then
select mid into v_mainid
from (select feetemp.id mid from UDM_P_THIRD_SALARYTEMP feetemp where feetemp.endor_no = v_endor_no and feetemp.fee_amount!=0
                                                                                          and feetemp.hedge_flag='N' order by feetemp.id desc)
where rownum=1;

update UDM_P_THIRD_SALARYTEMP tt set tt.fee_amount = tt.fee_amount-v_fee_amount,
                                     tt.fee_amount_cny = tt.fee_amount_cny-v_fee_amount_cny,tt.taildiff_flag='Y'
where tt.id = v_mainid;
end if;
end if;
end if;
end if;

Exception
        when others then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdsalaryexpand',sysdate,v_endor_no,sqlcode,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'--'||substr(sqlerrm,1,1000),'udm_p_thirdsalaryexpand');
            raise;
end;

    /**
      保证金额对再进行下一步
    */
    procedure udm_p_thirdtemp_check(v_policy_no in varchar2,v_endor_no in varchar2,exceptCode out varchar2) is
        v_count number(5):=0;
        v_amount number(18,2):=0;
        v_exceptmsg varchar2(4000);
begin
        --   DBMS_OUTPUT.put_line('=校验ing=');
        --保费表校验
select count(1) into v_count
from udm_policy_main aa,(select b.endor_no,sum(b.premium_amount) premium_amount from UDM_P_THIRDPREMIUMTEMP b group by b.endor_no) bb
where aa.endor_no = bb.endor_no and abs(aa.premium_amount - bb.premium_amount) > 0.03  and aa.endor_no=v_endor_no;
if v_count>0 and 1=2 then
select sum(b.premium_amount) into v_amount from UDM_P_THIRDPREMIUMTEMP b;
exceptCode := exceptCode||'保费交易币校验不通过,'||v_amount||';';
                UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'保费交易币不一致,展开值='||to_char(v_amount),'udm_p_thirdtemp_check');
end if;
select count(1) into v_count
from udm_policy_main aa,(select b.endor_no,sum(b.premium_amount_cny) premium_amount_cny from UDM_P_THIRDPREMIUMTEMP b group by b.endor_no) bb
where aa.endor_no = bb.endor_no and abs(aa.premium_amount_cny - bb.premium_amount_cny) > 0.03  and aa.endor_no=v_endor_no;
/*if v_count>0 then
    select sum(b.premium_amount_cny) into v_amount from UDM_P_THIRDPREMIUMTEMP b;
    exceptCode := exceptCode||'保费本位币校验不通过,'||v_amount||';';
    UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'保费本位币不一致,展开值='||to_char(v_amount),'udm_p_thirdtemp_check');
end if;*/


select count(1) into v_count from UDM_P_THIRDPREMIUMTEMP b where b.premium_currency='CNY' and (b.premium_amount!=b.premium_amount_cny or
                                                                                                       b.extax_premium_amount!=b.extax_premium_amount_cny or b.tax_amount!=b.tax_amount_cny or b.totalpremium_amount!=b.totalpremium_amount_cny
            or b.totalextax_premium_amount!=b.totalextax_premium_amount_cny or b.totaltax_amount!=b.totaltax_amount_cny);
if v_count>0 and v_policy_no not in ('6078349012007000010') then
            exceptCode := exceptCode||'保费币种为CNY，本位币与交易币不一致,'||v_amount||';';
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'保费本位币不一致,展开值='||to_char(v_amount),'udm_p_thirdtemp_check');
end if;

        --每期保费都需要对上(剔除缴费计划变更的)
select count(1) into v_count
from (select a.endor_no,a.installment_sn,a.endor_type,sum(a.premium_amount) premium_amount,sum(a.premium_amount_cny) premium_amount_cny
      from UDM_P_THIRDPREMIUMTEMP a group by a.endor_no,a.installment_sn,a.endor_type) aa,udm_policy_pay_plan bb
where aa.endor_no = bb.endor_no
  and aa.installment_sn = bb.installment_sn
  and nvl(aa.endor_type,'0')!='1008'
          and (abs(aa.premium_amount-bb.premium_amount)>0.03 or abs(aa.premium_amount_cny-bb.premium_amount_cny)>0.03);
/*if v_count>0 then
    for p in (
    select aa.installment_sn,aa.premium_amount-bb.premium_amount as v_premium_amount_diff,aa.premium_amount_cny-bb.premium_amount_cny as v_premium_amount_cny_diff
    from (select a.endor_no,a.installment_sn,a.endor_type,sum(a.premium_amount) premium_amount,sum(a.premium_amount_cny) premium_amount_cny
          from UDM_P_THIRDPREMIUMTEMP a group by a.endor_no,a.installment_sn,a.endor_type) aa,udm_policy_pay_plan bb
    where aa.endor_no = bb.endor_no
      and aa.installment_sn = bb.installment_sn
      and nvl(aa.endor_type,'0')!='1008'
      and abs(aa.premium_amount-bb.premium_amount)>0.03 or abs(aa.premium_amount_cny-bb.premium_amount_cny)>0.03) loop
            --UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'分期保费对不上:'||p.installment_sn||','||p.v_premium_amount_cny_diff||','||p.v_premium_amount_cny_diff||';','udm_p_thirdtemp_check');
            --if length(v_exceptmsg)<3500 then
            --    v_exceptmsg := v_exceptmsg||p.v_premium_amount_cny_diff||','||p.v_premium_amount_cny_diff||';';
            --end if;
            select '' into v_exceptmsg from dual;
        end loop;

    exceptCode := exceptCode||'分期保费对不上;';
    UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'分期保费对不上'||v_exceptmsg,'udm_p_thirdtemp_check');
end if;*/

--手续费表校验
select count(1) into v_count
from udm_policy_comm_fee aa,
     (select b.endor_no,b.producer_code, sum(b.fee_amount) fee_amount from UDM_P_THIRDCOMM_FEEtemp b group by b.endor_no,b.producer_code) bb
where aa.endor_no = bb.endor_no and aa.producer_code=bb.producer_code and abs(aa.fee_amount - bb.fee_amount) > 0.03   and aa.endor_no=v_endor_no;
if v_count>0 then
            /*for p in (select b.fee_amount,b.producer_code  from UDM_P_THIRDCOMM_FEEtemp b) loop
                    v_exceptmsg := v_exceptmsg||'交:'||p.producer_code||'='||to_char(p.fee_amount)||';';
                end loop;*/
            exceptCode := exceptCode||'手续费校验不通过；';
end if;
select count(1) into v_count
from udm_policy_comm_fee aa,
     (select b.endor_no,b.producer_code, sum(b.fee_amount_cny) fee_amount_cny from UDM_P_THIRDCOMM_FEEtemp b group by b.endor_no,b.producer_code) bb
where aa.endor_no = bb.endor_no and aa.producer_code=bb.producer_code and abs(aa.fee_amount_cny - bb.fee_amount_cny) > 0.03  and aa.endor_no=v_endor_no;
if v_count>0 then
            for p in (select b.fee_amount_cny,b.producer_code  from UDM_P_THIRDCOMM_FEEtemp b) loop
                    v_exceptmsg := v_exceptmsg;--||'本:'||p.producer_code||'='||to_char(p.fee_amount_cny)||';';
end loop;
end if;
        if length(v_exceptmsg)>0 and 1=2 then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'手续费不一致,展开值='||v_exceptmsg,'udm_p_thirdtemp_check');
end if;


        v_exceptmsg:='';
        --销售费用表校验

        ---------------------------update at 2024/09/25兼容多共保人校验
        /**
        select count(1) into v_count
         from udm_policy_sales_fee aa,
         (select b.endor_no,b.fee_type, sum(b.fee_amount) fee_amount from UDM_P_THIRDSALES_FEEtemp b group by b.endor_no,b.fee_type) bb
        where aa.endor_no = bb.endor_no and aa.fee_type=bb.fee_type and aa.fee_amount!= bb.fee_amount  and aa.endor_no=v_endor_no;
        */

SELECT count(1) into v_count
FROM (
         SELECT a.endor_no, a.fee_type, SUM(a.fee_amount) AS fee_amount
         FROM udm_policy_sales_fee a
         WHERE a.endor_no = v_endor_no
         GROUP BY a.endor_no, a.fee_type
     ) aa
         JOIN (
    SELECT b.endor_no, b.fee_type, SUM(b.fee_amount) AS fee_amount
    FROM UDM_P_THIRDSALES_FEEtemp b
    GROUP BY b.endor_no, b.fee_type
) bb ON aa.endor_no = bb.endor_no AND aa.fee_type = bb.fee_type
WHERE abs(aa.fee_amount - bb.fee_amount) > 0.03;

----------------------------

if v_count>0 then
            exceptCode := exceptCode||'销售费用校验不通过；';
            /*for p in (select b.fee_amount,b.fee_type from UDM_P_THIRDSALES_FEEtemp b) loop
                    v_exceptmsg := v_exceptmsg||p.fee_type||'='||to_char(p.fee_amount)||';';
                end loop;*/

end if;
        ---------------------------update at 2024/09/25兼容多共保人校验
        /**
        select count(1) into v_count
         from udm_policy_sales_fee aa,
         (select b.endor_no,b.fee_type, sum(b.fee_amount_cny) fee_amount_cny from UDM_P_THIRDSALES_FEEtemp b group by b.endor_no,b.fee_type) bb
        where aa.endor_no = bb.endor_no and aa.fee_type=bb.fee_type and aa.fee_amount_cny!= bb.fee_amount_cny and aa.endor_no=v_endor_no;
        */

SELECT count(1) into v_count
FROM (
         SELECT a.endor_no, a.fee_type, SUM(a.fee_amount_cny) AS fee_amount_cny
         FROM udm_policy_sales_fee a
         WHERE a.endor_no = v_endor_no
         GROUP BY a.endor_no, a.fee_type
     ) aa
         JOIN (
    SELECT b.endor_no, b.fee_type, SUM(b.fee_amount_cny) AS fee_amount_cny
    FROM UDM_P_THIRDSALES_FEEtemp b
    GROUP BY b.endor_no, b.fee_type
) bb ON aa.endor_no = bb.endor_no AND aa.fee_type = bb.fee_type
WHERE abs(aa.fee_amount_cny - bb.fee_amount_cny) > 0.03;
---------------------------

if length(v_exceptmsg)=0 and v_count>0 then
            for p in (select b.fee_amount_cny,b.fee_type from UDM_P_THIRDSALES_FEEtemp b) loop
                    v_exceptmsg := v_exceptmsg||'本:'||p.fee_type||'='||to_char(p.fee_amount_cny)||';';
end loop;
end if;
        if length(v_exceptmsg) >0 and 1=2 then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'销售费用不一致,展开值='||v_exceptmsg,'udm_p_thirdtemp_check');
end if;


        --绩效费用表校验
select count(1) into v_count
from udm_policy_salary aa,
     (select b.endor_no,sum(b.fee_amount) fee_amount from UDM_P_THIRD_SALARYTEMP b group by b.endor_no) bb
where aa.endor_no = bb.endor_no and abs(aa.fee_amount - bb.fee_amount) > 0.03 and aa.endor_no=v_endor_no;
if v_count>0 and 1=2 then
            exceptCode := exceptCode||'绩效校验不通过；';
select sum(b.fee_amount) into v_amount from UDM_P_THIRD_SALARYTEMP b;
UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'绩效交易币不一致,展开值='||to_char(v_amount),'udm_p_thirdtemp_check');
end if;
select count(1) into v_count
from udm_policy_salary aa,
     (select b.endor_no,sum(b.fee_amount_cny) fee_amount_cny from UDM_P_THIRD_SALARYTEMP b group by b.endor_no) bb
where aa.endor_no = bb.endor_no and abs(aa.fee_amount_cny - bb.fee_amount_cny) > 0.03 and aa.endor_no=v_endor_no;
if v_count>0 and 1=2 then
select sum(b.fee_amount_cny) into v_amount from UDM_P_THIRD_SALARYTEMP b;
UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,'绩效本位币不一致,展开值='||to_char(v_amount),'udm_p_thirdtemp_check');
end if;

Exception
        when others then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtemp_check',sysdate,v_endor_no,sqlcode,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'--'||substr(sqlerrm,1,1000),'udm_p_thirdtemp_check');
            raise;
end;

    procedure udm_p_thirdtoBe(v_policy_no in varchar2,v_endor_no in varchar2)is
        v_count number(8);
        v_countudm  number(8);
        v_accperiod_new date;
        v_hisdata_flag VARCHAR2(1);
        v_statutory_accounting_period date;
        v_valid_date date;

begin
select count(1) into v_count From UDM_P_ERRORLOG t where t.logtype='udm_p_thirdtemp_check' and t.logtype=t.exceptionfun and bizno=v_endor_no and t.exceptiondate>sysdate-0.02/24 ;
select count(1) into v_countudm from UDM_P_THIRDPREMIUM tt where tt.policy_no=v_policy_no and tt.endor_no=v_endor_no;
select t.hisdata_flag,t.statutory_accounting_period,t.valid_date into v_hisdata_flag,v_statutory_accounting_period,v_valid_date  from UDM_P_THIRDPREMIUMTEMP t where t.endor_no=v_endor_no and rownum=1;
if v_count=0 and v_countudm=0 then
            if v_hisdata_flag='1' then
                v_accperiod_new:=v_statutory_accounting_period;
                if v_valid_date>v_statutory_accounting_period then
                    v_accperiod_new:=v_valid_date;
end if;
end if;
            --由temp统一写入正式3层表
insert into UDM_P_THIRDPREMIUM(id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
                               risk_code ,start_date,end_date,issue_date,uw_date ,transaction_date,valid_date,renew_flag,statutory_accounting_period ,us_accounting_period,
                               sale_system ,init_policy_no,inception_date,renewed_policy_no ,business_source ,business_type ,appli_code,
                               nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,insuredname,is_multi_insureds ,operator_code ,
                               guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
                               debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,installment_sn,installment_times ,payer_code,payer_name,coins_code,coins_name,
                               coins_share ,coins_fee_rate,coins_type,coins_flag,joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,
                               joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
                               k_end_date,k_valid_date,k_valid_date_old,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
                               premium_amount,premium_currency,premium_exrate,premium_amount_cny,vat_flag,taxfree_flag,tax_rate,extax_premium_amount,tax_amount,extax_premium_amount_cny,tax_amount_cny,/*totalpremium_amount_share ,premium_amount_share,*/
                               totalpremium_amount,totalextax_premium_amount,totaltax_amount,totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,TAILDIFF_FLAG,
                               Pay_Start_Date,Subject_Name,Crct_No,Policy_From,business_unit,warranty_period,Src_Sencondid,pre_crctno,src_crctno,producer_type, producer_code,accperiod_new, earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,iscbc,isgiftinsurance)
select id,second_Main_ID,sys_source,proposal_no,policy_no ,declaration_no,endorApply_no,endor_no,endor_sn,endor_type,cancel_reason ,issue_company ,
       risk_code ,start_date,end_date,issue_date,uw_date ,transaction_date,valid_date,renew_flag,trunc(statutory_accounting_period) ,us_accounting_period,
       sale_system ,init_policy_no,inception_date,renewed_policy_no ,business_source ,business_type ,appli_code,
       nationalitycode,enterprisenature,htrelationship,circdomesticind,appliename,insuredname,is_multi_insureds ,operator_code ,
       guarantee_days,upc_flag,domestic_flag ,internet_flag ,policy_type ,is_prepaid,is_oversea,is_renewable,policy_renewalbe_no ,business_year ,
       debitnote_no,dm_flag ,hisdata_flag,modifydata_flag ,installment_sn,installment_times ,payer_code,payer_name,coins_code,coins_name,
       coins_share ,coins_fee_rate,coins_type,coins_flag,joins_code,joins_name,joins_share ,joins_policy_no ,joins_endor_no,
       joins_type,joins_flag,kind_sn ,kind_code ,kind_name ,kind_type ,treaty_type ,ht_treaty_share ,treaty_group,k_start_date,
       k_end_date,k_valid_date,k_valid_date_old,suminsured_amount ,deductible_amount ,suminsured_currency ,suminsured_exrate ,suminsured_amount_cny ,deductible_amount_cny ,deductible_rate ,
       premium_amount,premium_currency,premium_exrate,premium_amount_cny,vat_flag,taxfree_flag,tax_rate,extax_premium_amount,tax_amount,extax_premium_amount_cny,tax_amount_cny,/*totalpremium_amount_share ,premium_amount_share,*/
       totalpremium_amount,totalextax_premium_amount,totaltax_amount,totalpremium_amount_cny,totalextax_premium_amount_cny,totaltax_amount_cny,HEDGE_SencondID,HEDGE_ThirdID,TAILDIFF_FLAG,
       Pay_Start_Date,Subject_Name,crct_no,Policy_From,business_unit,warranty_period,Src_Sencondid,pre_crctno,src_crctno,producer_type, producer_code,trunc(greatest(start_date,uw_date,valid_date,statutory_accounting_period)), earned_startdate, earned_enddate,lineofbusiness,lineofbusiness_lv4,iscbc,isgiftinsurance
from UDM_P_THIRDPREMIUMTEMP;

insert into UDM_P_THIRDCOMM_FEE(id,Main_id,SECOND_ID,policy_no,endor_no,
                                operator_code,agreement_code,producer_type,producer_code,producer_name,is_main_producer,producer_tax_rate,fee_rate,
                                fee_amount,extax_fee_amount, tax_amount,currency,exrate,fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
                                ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,Taildiff_Flag)
select id,Main_id,SECOND_ID,policy_no,endor_no,
       operator_code,agreement_code,producer_type,producer_code,producer_name,is_main_producer,producer_tax_rate,fee_rate,
       fee_amount,extax_fee_amount, tax_amount,currency,exrate,fee_amount_cny,extax_fee_amount_cny,tax_amount_cny,
       ccaltype,certificateno,confirmation_code,inquery_date,HEDGE_SencondID,HEDGE_ThirdID,Taildiff_Flag
from UDM_P_THIRDCOMM_FEEtemp ;

insert into UDM_P_THIRDSALES_FEE(
    id,main_id, second_id, policy_no,endor_no,crct_no,
    fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
    tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
    tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
    hedge_thirdid,taildiff_flag
)
select id,main_id, second_id, policy_no,endor_no,crct_no,
       fee_type,operator_code,fee_rate,fee_amount,extax_fee_amount,
       tax_amount,currency,tax_rate,fee_amount_cny,extax_fee_amount_cny,
       tax_amount_cny,ccaltype,is_fixed_rate,hedge_sencondid,
       hedge_thirdid,taildiff_flag
from  UDM_P_THIRDSALES_FEEtemp;

insert into UDM_P_THIRD_SALARY(
    id,main_id,second_id,policy_no ,endor_no,
    crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
    ccaltype,hedge_sencondid,hedge_thirdid,Taildiff_Flag
)
select id,main_id,second_id,policy_no ,endor_no,
       crct_no,operator_code,fee_rate,fee_amount,fee_amount_cny,
       ccaltype,hedge_sencondid,hedge_thirdid,Taildiff_Flag
from UDM_P_THIRD_SALARYTEMP;

--    DBMS_OUTPUT.put_line('2~3insert 数据');
else
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtoBe',sysdate,v_endor_no,sqlcode,'校验未通过不进行导入','udm_p_thirdtoBe');
            --  insert into UDM_P_THIRDPREMIUM_Test select * from UDM_P_THIRDPREMIUMTEMP;(create table UDM_P_THIRDPREMIUM_Test as select * from UDM_P_THIRDPREMIUMTEMP;)
            --  TEST_DATA_PREMIUM
	    --  insert into UDM_P_comm_fee_Test select * from UDM_P_THIRDCOMM_FEEtemp;
         --   insert into UDM_P_third_salary_Test select * from UDM_P_THIRD_SALARYTEMP;
         -- insert into UDM_P_third_sales_Test select * from UDM_P_THIRDSALES_FEEtemp;
end if;
Exception
        when others then
            UDM_P_PACLOG.UDM_P_ADDERRORLOG('udm_p_thirdtoBe',sysdate,v_endor_no,sqlcode,DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'--'||substr(sqlerrm,1,1000),'udm_p_thirdtoBe');
            raise;
end;
end udm_p_thirdExpandDATA;
